Searching Multiple rows using VLOOKUP with wildcard(*,?) lookup va
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. |
Searching Multiple rows using VLOOKUP with wildcard(*,?) lookup va
=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 |
Searching Multiple rows using VLOOKUP with wildcard(*,?) lookup va
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. |
Searching Multiple rows using VLOOKUP with wildcard(*,?) looku
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. |
All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com