Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard lookup | Excel Worksheet Functions | |||
vlookup - multiple lookup values | Excel Worksheet Functions | |||
lookup with multiple rows returned | Excel Discussion (Misc queries) | |||
Searching rows with multiple columns criteria | Excel Worksheet Functions | |||
VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions |