LOOKUP or MATCH/INDEX?

delboy275

New Member
Joined
May 5, 2022
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am using Excel 2016.

Question I have a table on sheet 1. first cell (B8) has a drop down list of distances 0 – 99, 100 - 199, 200 -299 etc. Second cell (C8) has drop down list of “types” A, B , C etc.

Sheet 3 has 3x different tables all with the same distances and a separate array list of “types”,

(1 array list of distances 0 – 99, 100 - 199, 200 -299 for types A & E types. (B3:L15)

1x array list of distances 0 – 99, 100 - 199, 200 -299 for types B, C, F & & G types (B18:L30)

1x array list of distances 0 – 99, 100 - 199, 200 -299 for types D & H types) (B33:L45)

I want the formulae to check which “distance” has been selected in cell B8, then check which “type” has been selected in cell C8, then select which of the three tables on sheet 3 to lookup use to output the correct value.

(Note: Sheet 3 has a list of distances down the left side and corresponding values to the right of the distances).



I will try and write it in plain english:

If (B8) is 0-99 metres and (C8) is type A or E then VLOOKUP table Sheet 2, (B3;L15) or

If (B8) is 100-200 metres and (C8) is type A or E then VLOOKUP table Sheet 3, (B4;L11) or



Also should this be done using VLOOKUP/HLOOKUP or MATCH/INDEX?

I am tying myself in knots.

Thanks in advance.

Del
 
Thanks for that.
I entered your formulae and it displays 100 in cell H4
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If you still have the parameters set per your test case 100 should be correct.
Play around with the parameters and satisfy yourself that it works.
Let me know how you go. I am login off now, so if you have any issues be precise in advising what parameters you are using, what result you got and what result you were expecting.

PS: Mapping Table = Lookup Table (specifically where you are translating one code to another eg Type A is being replaced with a 1.
 
Upvote 0
Tried it. It works great.
Finally is it possible on the input table cell (I4 shown in red) to show the value at the top of the relevant 3 lookup tables (shown in green)?
I have corrected the headings and added cell I4 but formula hasn't changed.

EBI Track 200 Calculations.xlsx
ABCDEFGHIJ
1EBI 200 CALCULATIONS INPUT TABLE
2
3TRACK LENGTH (M)FREQTX (Highest) VRX (Lowest) VTX divided by RXIrail Ratio ATRACK ΩΩ/KM
4550-649A1.21.11.09109.09100
5
6
EBI200
Cell Formulas
RangeFormula
F4F4=SUM(D4/E4)
G4G4=SUM(D4/E4*100)
H4H4=CHOOSE(VLOOKUP($C4,TYPEMapping,2,FALSE), INDEX(INDEX(NormalPower!$B$6:$L$15,MATCH($B4,NormalPower!$B$6:$B$15,0),0),MATCH($G4,INDEX(NormalPower!$B$6:$L$15,MATCH($B4,NormalPower!$B$6:$B$15,0),0),1)), INDEX(INDEX(NormalPower!$B$21:$L$30,MATCH($B4,NormalPower!$B$21:$B$30,0),0),MATCH($G4,INDEX(NormalPower!$B$21:$L$30,MATCH($B4,NormalPower!$B$21:$B$30,0),0),1)), INDEX(INDEX(NormalPower!$B$36:$L$45,MATCH($B4,NormalPower!$B$36:$B$45,0),0),MATCH($G4,INDEX(NormalPower!$B$36:$L$45,MATCH($B4,NormalPower!$B$36:$B$45,0),0),1)))
Cells with Data Validation
CellAllowCriteria
B4List=TrackLengths!$D$5:$D$20
C4List=TrackLengths!$I$5:$I$13



EBI Track 200 Calculations.xlsx
BCDEFGHIJKL
1NORMAL POWER
2
3Track Length (m)NORMAL POWER FREQUENCIES A and E BALLEST IMPEDANCE ΩkM
42 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm101520200
5ΩkmΩkmΩkmΩkm
6200-24989929495969798989999
7250-349818790929395969798100
8350-449738186889093949697100
9450-549637480848790929596100
10550-649546674798287899395100
11650-749445867737783869194100
12750-849365059667178838992100
13850-949294252606574798690100
14950-1049243645535968748388100
151050-1100193039475363708086100
16Table 21 Normal Power Frequencies A and E Ballast Impedance
17
18Track ength (m)Normal Power Frequencies B, C, F and G Ballast Impedance Ωkm
192 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm101520200
20ΩkmΩkmΩkmΩkm
21200-249859092949596979898100
22250-349768387909193959797100
23350-449657581858791929596100
24450-549546674798287909395100
25550-649435766727783869194100
26650-749344857657077828992100
27750-849273949576372778690100
28850-949213242505666728287100
29950-1049172735434959677884100
301050-1100132230374353617481100
31Table 22 Normal Power Frequencies B, C, F and G Ballast Impedance
32
33Track ength (m)Normal Power Frequencies D and H Ballast Impedance Ωkm
342 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm101520200
35ΩkmΩkmΩkmΩkm
36200-249818790929395969798100
37250-349718084878992949697100
38350-449607178828588919496100
39450-549486170757984889294100
40550-649385261687379848992100
41650-749304353606674798690100
42750-849233544525868748388100
43850-949182837455161687985100
44950-1049142331384555627582100
451050-1100111926333949577078100
46Table 23 Normal Power Frequencies D and H Ballast Impedance
NormalPower
 
Upvote 0
I can probably get you the headings as long as you get rid of your merged cells eg row 5 would be 2 ohms to 200 ohms
The formulas will be even longer than the current ones though.

Since you are not using Tables or Range Names getting rid of merged cells will probably move the Table body range rows which means adjusting the formulas.
So I would need an new XL2BB of the NormalPower Sheet
 
Upvote 0
I have now removed the merged cells on lines 5, 20 & 35.
See below.
Thanks

EBI Track 200 Calculations.xlsx
ABCDEFGHIJ
1EBI 200 CALCULATIONS INPUT TABLE
2
3TRACK LENGTH (M)FREQTX (Highest) VRX (Lowest) VTX divided by RXIrail Ratio ATRACK ΩΩ/KM
4550-649A1.21.11.09109.09100
5
6
EBI200
Cell Formulas
RangeFormula
F4F4=SUM(D4/E4)
G4G4=SUM(D4/E4*100)
H4H4=CHOOSE(VLOOKUP($C4,TYPEMapping,2,FALSE), INDEX(INDEX(NormalPower!$B$6:$L$15,MATCH($B4,NormalPower!$B$6:$B$15,0),0),MATCH($G4,INDEX(NormalPower!$B$6:$L$15,MATCH($B4,NormalPower!$B$6:$B$15,0),0),1)), INDEX(INDEX(NormalPower!$B$21:$L$30,MATCH($B4,NormalPower!$B$21:$B$30,0),0),MATCH($G4,INDEX(NormalPower!$B$21:$L$30,MATCH($B4,NormalPower!$B$21:$B$30,0),0),1)), INDEX(INDEX(NormalPower!$B$36:$L$45,MATCH($B4,NormalPower!$B$36:$B$45,0),0),MATCH($G4,INDEX(NormalPower!$B$36:$L$45,MATCH($B4,NormalPower!$B$36:$B$45,0),0),1)))
Cells with Data Validation
CellAllowCriteria
B4List=TrackLengths!$D$5:$D$20
C4List=TrackLengths!$I$5:$I$13



EBI Track 200 Calculations.xlsx
ABCDEFGHIJKL
1NORMAL POWER
2
3Track Length (m)NORMAL POWER FREQUENCIES A and E BALLEST IMPEDANCE ΩkM
4
52 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm10 Ωkm15 Ωkm20 Ωkm200 Ωkm
6200-24989929495969798989999
7250-349818790929395969798100
8350-449738186889093949697100
9450-549637480848790929596100
10550-649546674798287899395100
11650-749445867737783869194100
12750-849365059667178838992100
13850-949294252606574798690100
14950-1049243645535968748388100
151050-1100193039475363708086100
16Table 21 Normal Power Frequencies A and E Ballast Impedance
17
18Track ength (m)Normal Power Frequencies B, C, F and G Ballast Impedance Ωkm
19
202 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm10 Ωkm15 Ωkm20 Ωkm200 Ωkm
21200-249859092949596979898100
22250-349768387909193959797100
23350-449657581858791929596100
24450-549546674798287909395100
25550-649435766727783869194100
26650-749344857657077828992100
27750-849273949576372778690100
28850-949213242505666728287100
29950-1049172735434959677884100
301050-1100132230374353617481100
31Table 22 Normal Power Frequencies B, C, F and G Ballast Impedance
32
33Track ength (m)Normal Power Frequencies D and H Ballast Impedance Ωkm
34
352 Ωkm3 Ωkm4 Ωkm5 Ωkm6 Ωkm8 Ωkm10 Ωkm15 Ωkm20 Ωkm200 Ωkm
36200-249818790929395969798100
37250-349718084878992949697100
38350-449607178828588919496100
39450-549486170757984889294100
40550-649385261687379848992100
41650-749304353606674798690100
42750-849233544525868748388100
43850-949182837455161687985100
44950-1049142331384555627582100
451050-1100111926333949577078100
46Table 23 Normal Power Frequencies D and H Ballast Impedance
NormalPower
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top