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.