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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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:


Excel 2010
ABCDEFGHI
1LetterNumberRecommendationLetterRecommendationMinimum Number
2A3PIGAPIG0
3A12DOGAHORSE5
4A7HORSEADOG10
5BCAT7
6CFISH3
Sheet1
Cell Formulas
RangeFormula
C2=LOOKUP(2,1/(($G$2:$G$6=A2)*($I$2:$I$6<=B2)),$H$2:$H$6)
 

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,489
Messages
5,837,661
Members
430,509
Latest member
steve85215

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