Lookup value in a table with multiple checks

[Nicobisgaard]

Hey Forum,
i want to try and find a smarter way than i use currently to lookup in a table based on 4 different checks.
The table looks like the one below:

https://photos.app.goo.gl/8N4FLdKwzBOzIqMo2

The first thing want is to check cell B3 - If the cell value is 3200 i want to lookup in column E:L. If the cell is 3700 i want to look up in M:T.
After that check i want to look in cell B4. If the value is "K12", and L was 3200, i want to look in columns E:F.
Third check is to look in cell B2, if the value is "Ma" i want to look at column F. Finally i want to look up h to find the correct value.

In the above example the correct value would be the one marked with green.

Does anyone have a good idea on how to achieve the above?
At the moment i am using a ton of If/And/Or sentences in a very very long code to do it - But i assume there must be some smarter way of doing the search?.

Thanks.

 

[thisoldman]

Your current lookup table header cells look like this:

I assume the first and second rows of the header rows contain merged cells. Merged cells make look-ups difficult.

If you change your header cells to the following, you can use an INDEX+MATCH+MATCH formula, entered as an array formula, to do the lookups.

Excel 2012
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	NRd	3200	3200	3200	3200	3200	3200	3200	3200	3700	3700	3700	3700	3700	3700	3700	3700
3	h	K12	K12	K10	K10	K08	K08	K06	K06	K12	K12	K10	K10	K08	K08	K06	K06
4	mm	Vi	Ma	Vi	Ma	Vi	Ma	Vi	Ma	Vi	Ma	Vi	Ma	Vi	Ma	Vi	Ma
Sheet1

Three different formulas, v1, v2, and v3:

Excel 2012
	A	B
2	LastKomb.	Ma
3	L	3200
4	Reinf	K08
5	h	30
6	Nrd v1	22
7	Nrd v2	22
8	Nrd v3	22
Sheet1

Cell Formulas
Range	Formula
B7	=INDEX(E5:T9, MATCH(B5, D5:D9, 0), MATCH(B3 & B4 & B2, {"3200K12Vi","3200K12Ma","3200K10Vi","3200K10Ma","3200K08Vi","3200K08Ma","3200K06Vi","3200K06Ma","3700K12Vi","3700K12Ma","3700K10Vi","3700K10Ma","3700K08Vi","3700K08Ma","3700K06Vi","3700K06Ma"}, 0))
B8	=INDEX(E5:T9, MATCH(B5, D5:D9, 0), MATCH(B3 & B4 & B2, V3:V18, 0))
B6	{=INDEX(E5:T9, MATCH(B5, D5:D9, 0), MATCH(B3 & B4 & B2, E2:T2 & E3:T3 & E4:T4, 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Cell B6 is the array formula.

In Cell B7, I've hard-coded the values for the column lookup.

In cell B8, I've used a helper column to find the proper column of the lookup table. The helper column looks like this (only part of the column is shown here):

Excel 2012
	V
2	HelperColumn
3	3200K12Vi
4	3200K12Ma
5	3200K10Vi
6	3200K10Ma
7	3200K08Vi
8	3200K08Ma
Sheet1

I hope this helps.

 

[Nicobisgaard]

Wow - Really great solution! The 2nd one is awesome, wish i had come up with that myself.

Thank you.

 

[thisoldman]

I'm happy to have helped. Thanks for the complement.