Specifying how VLOOKUP searches a column

[budakhan]

Hi,

i'm trying to figure out if there's a way to specify how VLOOKUP searches a column.

from the excel help files i understand it looks for the lookup_value in the 1st column of the table_array. The problem i have is that the value i want to use as the lookup_value appears multiple times in the 1st column.

So is there a way i can make it look up the 1st column from where i'm typing the formula in till it comes across the lookup_value?

ie. say cells A1 and A5 both contain the text "X"

if i type in cell D10 =VLOOKUP("X",A1:C20,3,FALSE) how do i get it to look for the last time "X" was used in the column A starting from A10?

this just about makes sense in my head but if you need anymore info let me know!

Cheers

 

[budakhan]

anyone have an idea how to do this?

meant to add to my example that D10 would ultimatley return the value in cell C5 if i could get the formula right...

 

[MrVillareal]

Hi try this sample.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFF00;;">x</td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">x</td><td style="text-align: right;background-color: #FFFF00;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFF00;;">a</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFF00;;">b</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFF00;;">c</td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFF00;;">x</td><td style="text-align: right;background-color: #FFFF00;;">5</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">Sheet4</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>Array 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">E1</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">A1:A5=D1,ROW(<font color="Green">A1:A5</font>)</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[budakhan]

thanks for the reply!

tried what you said but it didn't work on my spreadsheet, don't think i explained it right...

here's an example of my spreadsheet:

[IMG=http://img854.imageshack.us/img854/9365/exampler.jpg][/IMG]

so in cells C2, C3 and C4 i need a formula that will look up column A for the next "X" value and return the value in column B. So C2, C3 and C4 will end up showing the value "2"

In cells C10, C11 and C12 using the same formula they would show the value "4"

there's a bit more to it after this but thought it'd be best to see if this is possible first...

thanks for your help!

 

[budakhan]

oh by the way the example is shown in excel 2007 but at work the actual spreadsheet is on excel 2003, not sure if that'll make a difference....

 

[MrVillareal]

that formula should be confirm by Ctrl Shift Enter.

I thought your looking for the last "X" to return its value.

Can you provide expected result?

 

[Aladin Akyurek]

Hi,

i'm trying to figure out if there's a way to specify how VLOOKUP searches a column.

from the excel help files i understand it looks for the lookup_value in the 1st column of the table_array. The problem i have is that the value i want to use as the lookup_value appears multiple times in the 1st column.

So is there a way i can make it look up the 1st column from where i'm typing the formula in till it comes across the lookup_value?

ie. say cells A1 and A5 both contain the text "X"

if i type in cell D10 =VLOOKUP("X",A1:C20,3,FALSE) how do i get it to look for the last time "X" was used in the column A starting from A10?

this just about makes sense in my head but if you need anymore info let me know!

Cheers

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF(A1:A10="X",1),C1:C20)

Or just enter:

=LOOKUP(9.99999999999999E+307,1/(A1:A10="X"),C1:C20)