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]

Hi Alex.
Does your formulae require the 3x lookup tables to be on the same sheet as the input table as mine are currently on another sheet?

Secondly, how did I copy and paste the tables to show the column and row numbers as you have done above?

Regards.

 

[Alex Blakenburg]

Does your formulae require the 3x lookup tables to be on the same sheet as the input table as mine are currently on another sheet?

No they don't need to be on the same sheet.
One of the benefits of using Excel Tables is that their names have a scope of workbook. That is to say that when you reference them in a formula using Table referencing (Table Name and Column Name) you don't need to include a reference to the sheet name.

how did I copy and paste the tables to show the column and row numbers as you have done above?

I think you are referring to this:-
(Note: The orange download button is in the top right hand corner of the linked page)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[delboy275]

Can we take this a little further.
Cell H4
After checking cell B4 (550-649), then checking cell C4 ( frequency A in this instance) then after finding the correct table it needs to find the nearest match horizontally to the value in G4 (109.09), (in this instance the (closest match is 100), then display the value at the top of the column (i.e 200 in this instance.).
Hope this makes sense.
Are you on WhatsApp or messenger in case you have any questions?
I am not sure if we are allowed to put contact info on here.

 

[delboy275]

Can we take this a little further.
Cell H4
After checking cell B4 (550-649), then checking cell C4 ( frequency A in this instance) then after finding the correct table it needs to find the nearest match to the value in G4 (109.09) horizontally, (in this instance the (closest match is 100), then display the value at the top of the column (i.e 200 in this instance.).
Hope this makes sense.
Are you on WhatsApp or messenger in case you have any questions?
I am not sure if we are allowed to put contact info on here.

 

[delboy275]

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

Cell Formulas
Range	Formula
F4	F4	=SUM(D4/E4)
G4	G4	=SUM(D4/E4*100)
H4	H4	=VLOOKUP($B4,INDIRECT(VLOOKUP($C4,EBI200_TRACK_TABLE,2,FALSE)),2,FALSE)

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
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	050-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	050-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	050-1100	11	19	26	33	39	49	57	70	78	100
46	Table 23 Normal Power Frequencies D and H Ballast Impedance
NormalPower (2)

 

[Alex Blakenburg]

The XL2BB of NormalPower (2) does not look like they are Excel Tables. Excel Tables would have trashed your merged cells which are considered to be very bad practice by most experienced excel users.

I am on MS 365 so I don't know whether some formulas only work on my version or whether they will work on your Office 2016.
Put the formula below anywhere on NormalPower (2) (or the sheet with those 3 tables on them) and see if works. On my laptop it returns 93 (it is using 550-649 & 94 as the lookup values).
If it works and doesn't error out then I will explain how to implement a formula based around that formula.

=INDEX(INDEX($B$6:$L$15,MATCH("550-649",$B$6:$B$15,0),0),MATCH(94,INDEX($B$6:$L$15,MATCH("550-649",$B$6:$B$15,0),0),1))

I do need to know that:

• You have created a mapping table called EBI200_TRACK_TABLE
• Hit Ctrl+<F3> it will bring up the name manager. Make sure the Names and RefersTo columns are wide enough to show the full content. Take a screen shot and post it in your reply.
• Confirm that the sheet NormalPower (2), is your current sheet name for your 3 tables and that the XL2BB data sheet is accurrate ie Table 1 starts in B3 and Table 3 ends in L45 with the table name in row 46

 

[delboy275]

I had to upgrade yesterday to MS365 as XL2BB kept crashing my office 2016, so what I sent you yesterday was MS 365 version.
I used the name manager to show the table and it shows it as EBI200_TRACK_TABLE covering cells B3-L46. (the 3 tables are as exactly as shown in the example I sent you yesterday; Although the 3 tables are shown separately with each section named below it, they are all actually grouped as one called "EBI200_TRACK_TABLE"
I put your new formula on the input sheet cell H4 and it came up with a value of 1.090909.
I then entered your formulae on the mapping sheet with the three tables and it came up with a value of 93
BTW I have a NORMALPOWER sheet and a NORMALPOWER (2) sheet which for testing out your formulas. That is the correct names.
The input sheet is called EBI200.
See name manager photos attached.

 

[Alex Blakenburg]

You don't seem to have created the mapping table we discussed.
Please create this table and give it a name and tell me what that name is.

20220505 Select Different Lookup Table delboy275.xlsx
	O	P
3	Types	Table
4	A	1
5	B	2
6	C	2
7	D	3
8	E	1
9	F	2
10	G	2
11	H	3
OPs Data Power

 

[delboy275]

Sorry I will have to look that up as I have no idea what a mapping table is.

 

[Alex Blakenburg]

Copy that table (above) into your workbook, it doesn't matter where.
Click inside the table and hit Ctrl+T, click the "it has headings box"
Click on Table Design and in the far left corner in the Name box, copy in this name TYPEMapping

Once done then in H4 of EBI200 copy in the formula below.

I am about to log off it is nearly 1am here.

=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)))