LOOKUP or MATCH/INDEX?

[delboy275]

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

 

[delboy275]

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

 

[Alex Blakenburg]

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.

 

[delboy275]

will do, thanks again

 

[delboy275]

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
	A	B	C	D	E	F	G	H	I	J
1		EBI 200 CALCULATIONS INPUT TABLE
2
3		TRACK LENGTH (M)	FREQ	TX (Highest) V	RX (Lowest) V	TX divided by RX	Irail Ratio A	TRACK Ω	Ω/KM
4		550-649	A	1.2	1.1	1.09	109.09	100
5
6
EBI200

Cell Formulas
Range	Formula
F4	F4	=SUM(D4/E4)
G4	G4	=SUM(D4/E4*100)
H4	H4	=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
Cell	Allow	Criteria
B4	List	=TrackLengths!$D$5:$D$20
C4	List	=TrackLengths!$I$5:$I$13

EBI Track 200 Calculations.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1				NORMAL POWER
2
3	Track Length (m)	NORMAL POWER FREQUENCIES A and E BALLEST IMPEDANCE ΩkM
4		2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10	15	20	200
5								Ωkm	Ωkm	Ωkm	Ωkm
6	200-249	89	92	94	95	96	97	98	98	99	99
7	250-349	81	87	90	92	93	95	96	97	98	100
8	350-449	73	81	86	88	90	93	94	96	97	100
9	450-549	63	74	80	84	87	90	92	95	96	100
10	550-649	54	66	74	79	82	87	89	93	95	100
11	650-749	44	58	67	73	77	83	86	91	94	100
12	750-849	36	50	59	66	71	78	83	89	92	100
13	850-949	29	42	52	60	65	74	79	86	90	100
14	950-1049	24	36	45	53	59	68	74	83	88	100
15	1050-1100	19	30	39	47	53	63	70	80	86	100
16	Table 21 Normal Power Frequencies A and E Ballast Impedance
17
18	Track ength (m)	Normal Power Frequencies B, C, F and G Ballast Impedance Ωkm
19		2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10	15	20	200
20								Ωkm	Ωkm	Ωkm	Ωkm
21	200-249	85	90	92	94	95	96	97	98	98	100
22	250-349	76	83	87	90	91	93	95	97	97	100
23	350-449	65	75	81	85	87	91	92	95	96	100
24	450-549	54	66	74	79	82	87	90	93	95	100
25	550-649	43	57	66	72	77	83	86	91	94	100
26	650-749	34	48	57	65	70	77	82	89	92	100
27	750-849	27	39	49	57	63	72	77	86	90	100
28	850-949	21	32	42	50	56	66	72	82	87	100
29	950-1049	17	27	35	43	49	59	67	78	84	100
30	1050-1100	13	22	30	37	43	53	61	74	81	100
31	Table 22 Normal Power Frequencies B, C, F and G Ballast Impedance
32
33	Track ength (m)	Normal Power Frequencies D and H Ballast Impedance Ωkm
34		2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10	15	20	200
35								Ωkm	Ωkm	Ωkm	Ωkm
36	200-249	81	87	90	92	93	95	96	97	98	100
37	250-349	71	80	84	87	89	92	94	96	97	100
38	350-449	60	71	78	82	85	88	91	94	96	100
39	450-549	48	61	70	75	79	84	88	92	94	100
40	550-649	38	52	61	68	73	79	84	89	92	100
41	650-749	30	43	53	60	66	74	79	86	90	100
42	750-849	23	35	44	52	58	68	74	83	88	100
43	850-949	18	28	37	45	51	61	68	79	85	100
44	950-1049	14	23	31	38	45	55	62	75	82	100
45	1050-1100	11	19	26	33	39	49	57	70	78	100
46	Table 23 Normal Power Frequencies D and H Ballast Impedance
NormalPower

 

[Alex Blakenburg]

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

 

[delboy275]

I have now removed the merged cells on lines 5, 20 & 35.
See below.
Thanks

EBI Track 200 Calculations.xlsx
	A	B	C	D	E	F	G	H	I	J
1		EBI 200 CALCULATIONS INPUT TABLE
2
3		TRACK LENGTH (M)	FREQ	TX (Highest) V	RX (Lowest) V	TX divided by RX	Irail Ratio A	TRACK Ω	Ω/KM
4		550-649	A	1.2	1.1	1.09	109.09	100
5
6
EBI200

Cell Formulas
Range	Formula
F4	F4	=SUM(D4/E4)
G4	G4	=SUM(D4/E4*100)
H4	H4	=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
Cell	Allow	Criteria
B4	List	=TrackLengths!$D$5:$D$20
C4	List	=TrackLengths!$I$5:$I$13

EBI Track 200 Calculations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1					NORMAL POWER
2
3		Track Length (m)	NORMAL POWER FREQUENCIES A and E BALLEST IMPEDANCE ΩkM
4
5			2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10 Ωkm	15 Ωkm	20 Ωkm	200 Ωkm
6		200-249	89	92	94	95	96	97	98	98	99	99
7		250-349	81	87	90	92	93	95	96	97	98	100
8		350-449	73	81	86	88	90	93	94	96	97	100
9		450-549	63	74	80	84	87	90	92	95	96	100
10		550-649	54	66	74	79	82	87	89	93	95	100
11		650-749	44	58	67	73	77	83	86	91	94	100
12		750-849	36	50	59	66	71	78	83	89	92	100
13		850-949	29	42	52	60	65	74	79	86	90	100
14		950-1049	24	36	45	53	59	68	74	83	88	100
15		1050-1100	19	30	39	47	53	63	70	80	86	100
16		Table 21 Normal Power Frequencies A and E Ballast Impedance
17
18		Track ength (m)	Normal Power Frequencies B, C, F and G Ballast Impedance Ωkm
19
20			2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10 Ωkm	15 Ωkm	20 Ωkm	200 Ωkm
21		200-249	85	90	92	94	95	96	97	98	98	100
22		250-349	76	83	87	90	91	93	95	97	97	100
23		350-449	65	75	81	85	87	91	92	95	96	100
24		450-549	54	66	74	79	82	87	90	93	95	100
25		550-649	43	57	66	72	77	83	86	91	94	100
26		650-749	34	48	57	65	70	77	82	89	92	100
27		750-849	27	39	49	57	63	72	77	86	90	100
28		850-949	21	32	42	50	56	66	72	82	87	100
29		950-1049	17	27	35	43	49	59	67	78	84	100
30		1050-1100	13	22	30	37	43	53	61	74	81	100
31		Table 22 Normal Power Frequencies B, C, F and G Ballast Impedance
32
33		Track ength (m)	Normal Power Frequencies D and H Ballast Impedance Ωkm
34
35			2 Ωkm	3 Ωkm	4 Ωkm	5 Ωkm	6 Ωkm	8 Ωkm	10 Ωkm	15 Ωkm	20 Ωkm	200 Ωkm
36		200-249	81	87	90	92	93	95	96	97	98	100
37		250-349	71	80	84	87	89	92	94	96	97	100
38		350-449	60	71	78	82	85	88	91	94	96	100
39		450-549	48	61	70	75	79	84	88	92	94	100
40		550-649	38	52	61	68	73	79	84	89	92	100
41		650-749	30	43	53	60	66	74	79	86	90	100
42		750-849	23	35	44	52	58	68	74	83	88	100
43		850-949	18	28	37	45	51	61	68	79	85	100
44		950-1049	14	23	31	38	45	55	62	75	82	100
45		1050-1100	11	19	26	33	39	49	57	70	78	100
46		Table 23 Normal Power Frequencies D and H Ballast Impedance
NormalPower