Given data_array:

abc 1000
abx 2000
bcd 3000
def 4000
bhg 3200

and lookup_value is b??, how can we obtain result with

bcd 3000
bhg 3200

using VLOOKUP

thanks.

=vlookup() returns a single value.

Maybe you could filter (data|filter|autofilter) and show the cells that begin
with B.

Tommy wrote:

Given data_array:

abc 1000
abx 2000
bcd 3000
def 4000
bhg 3200

and lookup_value is b??, how can we obtain result with

bcd 3000
bhg 3200

using VLOOKUP

thanks.

--

Dave Peterson

For XL-2007:

=IFERROR(INDEX(rngA,SMALL(IF(LEFT(rngA)="b",ROW(IN DIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down as far as needed


For older versions of Excel:

=IF(ISERR(SMALL(IF(LEFT(rngA)="b",ROW(INDIRECT("1: "&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(I F(LEFT(rngA)="b",ROW(INDIRECT("1:"&ROWS(rngA)))),R OWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed



"Tommy" wrote:

Given data_array:

abc 1000
abx 2000
bcd 3000
def 4000
bhg 3200

and lookup_value is b??, how can we obtain result with

bcd 3000
bhg 3200

using VLOOKUP

thanks.

Teethless mama:

Sorry, I am not very clear how does your code operated. Could you please
apply your code to my excel file which provide you the actual difficulty that
I have.

Thanks.



http://www.yousendit.com/download/TT...TStwcFZjR0E9PQ


"Teethless mama" wrote:

For XL-2007:

=IFERROR(INDEX(rngA,SMALL(IF(LEFT(rngA)="b",ROW(IN DIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down as far as needed


For older versions of Excel:

=IF(ISERR(SMALL(IF(LEFT(rngA)="b",ROW(INDIRECT("1: "&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(I F(LEFT(rngA)="b",ROW(INDIRECT("1:"&ROWS(rngA)))),R OWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed



"Tommy" wrote:

Given data_array:

abc 1000
abx 2000
bcd 3000
def 4000
bhg 3200

and lookup_value is b??, how can we obtain result with

bcd 3000
bhg 3200

using VLOOKUP

thanks.