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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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)
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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