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

 

[Alex Blakenburg]

With your table ranges being a bit all over the place, how about making all the Lookup Tables actual Excel tables ?
You can then simply use indirect to choose the table to lookup based the correct table name being used in the formula based on the types code.

See if this gives you any ideas.
Table Name tblTYPES

20220505 Select Different Lookup Table delboy275.xlsx
	A	B	C	D	E	F
1	Type	Lookup code	Actual formula		Types	Table
2	A	Orange	20		A	tblTypeAE
3	B	Orange	920		B	tblTypeBCFG
4					C	tblTypeBCFG
5					D	tblTypeBCFG
6					E	tblTypeAE
Data

Cell Formulas
Range	Formula
C2:C3	C2	=VLOOKUP(B2,INDIRECT(VLOOKUP(A2,tblTYPES,2,FALSE)),2,FALSE)

Table names per table tblTYPES above

20220505 Select Different Lookup Table delboy275.xlsx
	A	B	C	D	E	F	G	H
1	Col1	Col2	Col3			Col1	Col2	Col3
2	Apple	10				Apple	910
3	Orange	20				Orange	920
4	Pear	30				Pear	930
LookupTables

 

[delboy275]

Hi Alex,
Many thanks for your reply.
I am having trouble interpreting your formulae, but I have had a go with my spreadsheet. see below.
H4 is where I want the result to be.
I have 3x tables (TYPE_AE, TYPE_BCFG and TYPE_DH).

=VLOOKUP(H4,INDIRECT(VLOOKUP(C4,tblTYPESAE,2,FALSE)),2,FALSE)

but how do i put this into formulae?
Thanks.

	B	C	D	E	F	G	H	J	K
2
3	DIST	TYPE
4	550-649	A	1.2	1.1	1.09	109.09	0.00
5
6
7
8
9
10
11	TYPES A & E
12		2 Plums​	3 Plums​	4 Plums​	5 Plums​	6 Plums​	7 Plums​	8 Plums​	9 Plums​	10 Plums​	11 Plums​
13	200-249	89	92	94	95	96	97	98	98	99	99
14	250-349	81	87	90	92	93	95	96	97	98	100
15	350-449	73	81	86	88	90	93	94	96	97	100
16	450-549	63	74	80	84	87	90	92	95	96	100
17
18
19	TYPES B, C, F & G
20		2 Plums​	3 Plums​	4 Plums​	5 Plums​	6 Plums​	7 Plums​	8 Plums​	9 Plums​	10 Plums​	11 Plums​
21	200-249	89	92	94	95	96	97	98	98	99	99
22	250-349	81	87	90	92	93	95	96	97	98	100
23	350-449	73	81	86	88	90	93	94	96	97	100
24	450-549	63	74	80	84	87	90	92	95	96	100
25
26
27	TYPES D & H
28		2 Plums​	3 Plums​	4 Plums​	5 Plums​	6 Plums​	7 Plums​	8 Plums​	9 Plums​	10 Plums​	11 Plums​
29	200-249	89	92	94	95	96	97	98	98	99	99
30	250-349	81	87	90	92	93	95	96	97	98	100
31	350-449	73	81	86	88	90	93	94	96	97	100
32	450-549	63	74	80	84	87	90	92	95	96	100
33

 

[Alex Blakenburg]

1) You can't have H4 as both the formula result cell and a value in the Vlookup. Did you mean A4?

H4 is where I want the result to be.
=VLOOKUP(H4,INDIRECT(VLOOKUP(C4,tblTYPESAE,2,FALSE)),2,FALSE)

2) In your Row 4 - what determines which column to use in the other tables ie Column - 2 Plums 3 Plums 4 Plums etc

3) Your Example uses 550-649, which is not in any of the tables

4) Your Tables still don't look like Excel Tables (eg highlight B12 to L16 and hit Ctrl+T, Table has heading = Ticked, give the table a meaningful name.

5) You don't seem to have a Table Mapping your Type codes to the table names that you create.

Note: I would encourage to use Tables but you can get by with Range Names.
You previously indicated your lookup tables were on different sheets but here you have everything on the one sheet. Which is it ?

 

[Alex Blakenburg]

For Tables have a look at this 3min video from Contextures

The accompanying article is here:-
How to Create Excel Tables and Fix Excel Table Problems

One of the benefits in this case, is that you don't need to know the sheet name of the table, you can just refer to the Table Name.
The fact that they autoexpand is also helpful for mapping / lookup tables.

 

[delboy275]

HI Alex,
Sorry I am getting confused with excel.

2. In row 4 the distance and Type A, B C, etc) will decide which table to use. (I know the distances are the same in the tables but other tables will be added later).
As shown Type A or E can only use the top table. Types B, C, F or G can only use the second table etc.

3. please replace 550-649 with 450-549. (It's because I didn't show you the full tables)

4. I have renamed each table as follows: NP_AE, NP_BCFG & NP_DH in the order they are shown (to separate the different "TYPES").

5. I think I need to start afresh with the coding.

6. The lookup tables have been created and are on different sheets as the tables are quite large. I extracted bits and put onto one sheet as I didn't want to breach confidentiality and hopefully to make it easier for you to view.
Regards.

 

[Alex Blakenburg]

I have logged off for the night and will have a look tomorrow and put it together using your table names.
What do you want the table to be called that links the Type to the right lookup Table ? So I can use that in the formula. (Or did you call it TYPES)

 

[delboy275]

If we call the Table "EBI200_TRACK_TABLE".
Thanks again for your help.
Have a good night.
Tomorrow I am busy so will get back to you when possible.
Regards.

 

[Alex Blakenburg]

If you have given all the tables the names as above this should work.
Formula H

20220505 Select Different Lookup Table delboy275.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		DIST	TYPE												Types	Table
4		550-649	A	1.2	1.1	1.09	109.09	100							A	NP_AE
5															B	NP_BCFG
6															C	NP_BCFG
7															D	NP_DH
8															E	NP_AE
9															F	NP_BCFG
10															G	NP_BCFG
11															H	NP_DH
OPs Data

Cell Formulas
Range	Formula
H4	H4	=VLOOKUP($B4,INDIRECT(VLOOKUP($C4,EBI200_TRACK_TABLE,2,FALSE)),2,FALSE)

 

[delboy275]

=VLOOKUP($B4,INDIRECT(VLOOKUP($C4,EBI200_TRACK_TABLE,2,FALSE)),2,FALSE)

Hi Alex,
Many thanks for your reply.
I will check if your formulae works on Monday when I get home.
Thanks again