Lookup value in a table with multiple checks

Nicobisgaard

New Member
Joined
Feb 17, 2017
Messages
7
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.
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Your current lookup table header cells look like this:

r0hVKb6.png


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
DEFGHIJKLMNOPQRST
2NRd3200320032003200320032003200320037003700370037003700370037003700
3hK12K12K10K10K08K08K06K06K12K12K10K10K08K08K06K06
4mmViMaViMaViMaViMaViMaViMaViMaViMa
Sheet1

Three different formulas, v1, v2, and v3:

Excel 2012
AB
2LastKomb.Ma
3L3200
4ReinfK08
5h30
6Nrd v122
7Nrd v222
8Nrd v322
Sheet1
Cell Formulas
RangeFormula
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
2HelperColumn
33200K12Vi
43200K12Ma
53200K10Vi
63200K10Ma
73200K08Vi
83200K08Ma
Sheet1

I hope this helps.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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