# Complex Vlookup Question

#### rhhrock

##### New Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
5BCAT7
6CFISH3
Sheet1
Cell Formulas
RangeFormula
C2=LOOKUP(2,1/((\$G\$2:\$G\$6=A2)*(\$I\$2:\$I\$6<=B2)),\$H\$2:\$H\$6)

possibly this (hm seems to be essentially the same as above well on second look it isnt!)
Sheet1

 * A B C D E 1 A PIG 0 * * 2 A HORSE 5 * * 3 A DOG 10 * * 4 B CAT 7 * * 5 C FISH 3 * * 6 * * * * * 7 * * * * * 8 * * * * * 9 * * * * * 10 * * * * * 11 * * * * * 12 * * * * * 13 * * * * * 14 * * your example * formula 15 Letter Number Recommendation * * 16 A 3 Pig * PIG 17 A 12 Dog * DOG 18 A 7 Horse * HORSE 19 b 6 * * #N/A 20 c 8 * * FISH

<tbody>
</tbody>

 Cell Formula 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:
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!

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

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:

Replies
3
Views
352
Replies
3
Views
217
Replies
7
Views
160
Replies
6
Views
384
Replies
0
Views
503

1,216,410
Messages
6,130,423
Members
449,581
Latest member
econtent2

### 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.

### Which adblocker are you using?

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

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