Looking for VLOOKUP Trick

[shdawson]

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

 

[danzon]

Have a look at INDEX and MATCH functions

 

[dado6612]

Match and index works for looking to left

 

[shdawson]

Thanks, guys.

OK, have used MATCH, but not INDEX, let alone together.

Which is your "favorite"? A quick code example, if you would please?


Thanks,
S

 

[Richard Schollar]

<b>Excel 2010</b><table width="90%" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</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;;">28</td><td style=";">Richard</td></tr><tr ><td style="color: #161120;text-align: center;">2</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;;">33</td><td style=";">Ted</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Name</td><td style=";">Sally</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style=";">Sally</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Age</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">35</td><td style=";">Shona</td></tr><tr ><td style="color: #161120;text-align: center;">5</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;;">23</td><td style=";">Bob</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;;">31</td><td style=";">Phil</td></tr><tr ><td style="color: #161120;text-align: center;">7</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;;">38</td><td style=";">Ian</td></tr><tr ><td style="color: #161120;text-align: center;">8</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;;"></td><td style="text-align: right;;"></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">E4</th><td style="text-align:left">=INDEX(<font color="Blue">$I$1:$I$7,MATCH(<font color="Red">E3,$J$1:$J$7,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[danzon]

=INDEX(A:A,MATCH("TIM",B:B,0))

Will find "TIM" in column B and return the corresponding value in column A

 

[dado6612]

Ex.
Your table is in A1:D10
In E1 is lookup value

= INDEX(A:A10,MATCH(E1,D1:D10,0))

Column D is where formula is finding matching value and return value from column A (opposite than vlookup)

 

[shdawson]

OK, got it.

Thanks, guys...have a great day.


SHD

 

[shdawson]

Hi,

Current Formula:

=INDEX('Tasks Report'!L$2:L$10000,MATCH(A2,'Tasks Report'!X$2:X$10000))

Formula works great. However, 1 row is coming up #N/A. Looked to see if the value in that row, A78, is there. Sure enough, it is there.

Looked at the value in the cell. No front or end spaces. Why would this be coming up as a #N/A, please?


Thanks,
S

 

[DonkeyOte]

You mentioned VLOOKUP tricks - demo'd by Barry Houdini the other day

=VLOOKUP(A2,CHOOSE({1,2},'Tasks Report'!X$2:X$10000,'Tasks Report'!L$2:L$10000),2,0)

In terms of your error - the MATCH should be exact I suspect:

=INDEX('Tasks Report'!L$2:L$10000,MATCH(A2,'Tasks Report'!X$2:X$10000,0))