Complex Vlookup Question

rhhrock

New Member
Joined
Mar 29, 2013
Messages
3
I consider myself an advanced excel user, but I can't quite figure this one out. Keep in mind, I need to figure out how to do this without using a macro.

Here's an example of what I'm doing. The cells in the Recommendation column in Table 1 is where the formula will need to go. Basically, I need to figure out a recommendation based on the Letter and Number. Let's take row 1 in Table 1 for example. I would need to somehow vlookup the Letter cell in Table 1 in the Table 2 array, and then figure out where the number in Table 1 falls in Table 2 (terrible explanation, I know).

Basically, the recommendation in the first row of Table 1 is Pig because if you look at Table 2, and only look at the rows for Letter A, Number 3 falls between 0 and 5, so the key says we should recommend Pig. How in the world would I do this in a formula?

TABLE 1 - Recommendations

Letter
Number
Recommendation
A
3
Pig
A
12
Dog
A
7
Horse

<tbody>
</tbody>








TABLE 2 - KEY

Letter
Recommendation
Minimum Number
A
PIG
0
A
HORSE
5
A
DOG
10
B
CAT
7
C
FISH
3

<tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Hi

Good explanation of the problem and the requirement.

Assuming the values in Table 2 are ordered ascending (based on Minimum number) you can use this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Letter</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Number</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Recommendation</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Letter</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Recommendation</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Minimum Number</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">PIG</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">PIG</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">DOG</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">HORSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">HORSE</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">DOG</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">B</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">CAT</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">C</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">FISH</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">$G$2:$G$6=A2</font>)*(<font color="Green">$I$2:$I$6<=B2</font>)</font>),$H$2:$H$6</font>)</td></tr></tbody></table></td></tr></table><br />
 

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
possibly this (hm seems to be essentially the same as above well on second look it isnt!)
Sheet1

*ABCDE
1APIG0**
2AHORSE5**
3ADOG10**
4BCAT7**
5CFISH3**
6*****
7*****
8*****
9*****
10*****
11*****
12*****
13*****
14**your example*formula
15LetterNumberRecommendation**
16A3Pig*PIG
17A12Dog*DOG
18A7Horse*HORSE
19b6**#N/A
20c8**FISH

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E16=LOOKUP(B16,1/($A$1:$A$5=A16)*$C$1:$C$5,$B$1:$B$5)
E17=LOOKUP(B17,1/($A$1:$A$5=A17)*$C$1:$C$5,$B$1:$B$5)
E18=LOOKUP(B18,1/($A$1:$A$5=A18)*$C$1:$C$5,$B$1:$B$5)
E19=LOOKUP(B19,1/($A$1:$A$5=A19)*$C$1:$C$5,$B$1:$B$5)
E20=LOOKUP(B20,1/($A$1:$A$5=A20)*$C$1:$C$5,$B$1:$B$5)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

rhhrock

New Member
Joined
Mar 29, 2013
Messages
3
Brilliant! Worked like a charm. I have been put in my place. I know only consider myself a kind of advanced, but not really that great excel user. :)

Would either of you be so kind as to walk me through what exactly the formula is doing in this case? In all of the excel models that I've built, I've never used the "Lookup" function, and I'd like to understand it a little better, especially within the context of this example.

Thanks again for your help. I really appreciate it!
 

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
take =LOOKUP(10,{0,5,20,30},{100,200,300,400}) from help files If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. so in this case it finds 5 the 2nd position in the array{0,5,20,30} which is the largest less than or = to 10
and returns the value from the 2 position in the array {100,200,300,400} ie 200
a quirk of lookup is it ignore errors and #DIV/0! is one of these
so if the array was
=LOOKUP(10,{0,#DIV/0!,20,30},{100,200,300,400}) it cant find 10 so finds the lowest<=10 ie 0 which is position 1 and returns 100
so we can make the lookup vector give #DIV/0! where we dont want the value for your data as i showed it for example 1/($A$1:$A$5=A18) or 1/($A$1:$A$5="A")gives {1,1,1,#DIV/0!,#DIV/0!} multiply that by your range of lower limits {0,5,10,7,3} you get an array of
{0,5,10,#DIV/0!,#DIV/0!}
now the lookup becomes
lookup(7,{0,5,10,#DIV/0!,#DIV/0!},{"PIG","HORSE","DOG","CAT","FISH"}) cant find 7 so finds 5 position 2 and returns Horse
(note the actual arrays when you look at the formula use ; instead of , as a separator)
HTH
 

rhhrock

New Member
Joined
Mar 29, 2013
Messages
3
Great explanation! I appreciate you taking the time to help me out. I'm definitely going to add this to my ever-growing excel toolbox.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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
Top