Searching Multiple rows using VLOOKUP with wildcard(*,?) lookup va

  • Thread starter Thread starter Tommy
  • Start date Start date
T

Tommy

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.
 
For XL-2007:

=IFERROR(INDEX(rngA,SMALL(IF(LEFT(rngA)="b",ROW(INDIRECT("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(IF(LEFT(rngA)="b",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

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