Vlookup - Combine two columns

[pto160]

I am trying to do a vlookup by combining two columns in the array to match the lookup value. I know how to this with Index,Match.
Can this be done with vlookup without using a helper column?
Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>A</td> <td>B</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>B</td> <td>D</td> <td style="text-align: right;">20</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>R</td> <td>T</td> <td style="text-align: right;">50</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td> </td> <td> </td> <td>Result</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Lookup</td> <td>BD</td> <td style="text-align: right;">20</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Dryver14]

Thanks Aladin

I have copied that info down and will go and have a play with it,

But I get the fact this is not the best method because its a CSE formula.

 

[Aladin Akyurek]

Thanks Aladin

I have copied that info down and will go and have a play with it,

But I get the fact this is not the best method because its a CSE formula.

No, it's not CSE...

The CHOOSE formula I used in order to construct the look up table in order to invoke VLOOKUP requires concatenation. The concatenation requirement leads to CSE, not the CHOOSE function per se.

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

is not a CSE formula, for exemple. LOOKUP with the big number is just fast on a two-value array constant CHOOSE builds with 0 and the VLOOKUP result.

 

[pto160]

Aladin, that works great. Thanks to everyone for the index match formulas as well.

 

[mgirvin]

Hey delaneyjm,

Got it! I was thinking that maybe you encounter data sets that usually have spaces and that was a cool safety move!

I like it as a safety move. I have added that trick to my Excel tool kit.

 

[mgirvin]

Hey Aladin,

Your example:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

was that just an example of how CHOOSE was not requiring CSE?

Or was:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

a potential solution to the original question?

 

[mgirvin]

If you wanted a non CSE with VLOOKUP, maybe:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=VLOOKUP(B6,CHOOSE({1,2},INDEX(A1:A3&B1:B3,),C1:C3),2,0)
<o></o>
Following Aladin's lead about how it is the concatenation that requires the CSE and ZVI’s note about the many permutations of INDEX-- just put the concatenation in INDEX with the row_num argument blank. Because the INDEX can handle arrays without CSE and because row_num argument blank yields all the rows it should work.

 

[Aladin Akyurek]

If you wanted a non CSE with VLOOKUP, maybe:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=VLOOKUP(B6,CHOOSE({1,2},INDEX(A1:A3&B1:B3,),C1:C3),2,0)
<o></o>
Following Aladin's lead about how it is the concatenation that requires the CSE and ZVI’s note about the many permutations of INDEX-- just put the concatenation in INDEX with the row_num argument blank. Because the INDEX can handle arrays without CSE and because row_num argument blank yields all the rows it should work.

The INDEX call, just in order to avoid pressing the CSE key combination, is expensive. I recall introducing the INDEX usage in this manner at this board eons ago, but since long I avoid doing so for that reason.

 

[mgirvin]

Yes, I remember when I learned using the INDEX to aviod CSE from you in a post about looking up last and first items in a list!

So when you say expensive, you mean it takes longer to calculate, right?

Also, what do you think about my question:

Hey Aladin,

Your example:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

was that just an example of how CHOOSE was not requiring CSE?

Or was:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

a potential solution to the original question?
​

 

[Aladin Akyurek]

Yes, I remember when I learned using the INDEX to aviod CSE from you in a post about looking up last and first items in a list!

So when you say expensive, you mean it takes longer to calculate, right?

Also, what do you think about my question:

Hey Aladin,

Your example:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

was that just an example of how CHOOSE was not requiring CSE?

Or was:

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

a potential solution to the original question?
​

See post #6 for the answer I've give to the original question, which is by the way a good exemple (if I may so) of doing union using CHOOSE. Something I experimented with very early (We asked (Juan and me) Longre to program such a function. Morefunc has ARRAY.UNION in that sense).

I provided

=LOOKUP(9.999999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

as another exemple with CHOOSE, which does not need CSE.

 

[mgirvin]

Yes, you may so. I always learn awesome robustified Excel facts from you!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have already studied post #6 and incorporated the facts into my Excel tool kit.
<o></o>
<o></o>
I was just wondering if there was another formula using LOOKUP. I see that it was just an example of the CHOOSE that does not require CSE.

Cool bit of history too!
<o></o>
<o></o>
Thanks!
<o></o>
<o></o>
P.S. I just wish I could hang out more at this Board and learn robustified facts every day, but I always seem to be working 80 hour weeks and no time... <o></o>