Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
looking up items in a list with wildcards
list one
col a col b 555* xyz 666?? abc List two 5556 5557 66622 I would like a formula which would look up the items in list two, search list one (which contains wildcard characters) and return the correct matching item. example: I would like to look up 5556 in list a and return xyz and look up 66622 and return abc. I can easily do in reverse, but I can't figure out how to do in this direction with the wildcards in the list a |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
looking up items in a list with wildcards
This sounds like a nightmare!
Wildcards don't work on numbers. Your list one starts with 3 digits. You *might* be able to use something like this: 555* = 555 and *all* other characters. 555?? = 555 and *any 2* characters So: 5556 will match 555* 55566 will match 555?? A10 = 5556: =VLOOKUP(LEFT(A10,3)&"*",list1,2,0) A11 = 55566: =VLOOKUP(LEFT(A11,3)&"??",list1,2,0) -- Biff Microsoft Excel MVP "lutherdail" wrote in message ... list one col a col b 555* xyz 666?? abc List two 5556 5557 66622 I would like a formula which would look up the items in list two, search list one (which contains wildcard characters) and return the correct matching item. example: I would like to look up 5556 in list a and return xyz and look up 66622 and return abc. I can easily do in reverse, but I can't figure out how to do in this direction with the wildcards in the list a |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
looking up items in a list with wildcards
You can use a single formula:
=VLOOKUP(LEFT(A10,3)&IF(LEN(A10)=4,"*","??"),list1 ,2,0) If the lookup_value is 4 characters then it uses the "*" wildcard, otherwise it uses the "??" wildcards. So, I'm assuming all lookup_values are either 4 or 5 characters. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... This sounds like a nightmare! Wildcards don't work on numbers. Your list one starts with 3 digits. You *might* be able to use something like this: 555* = 555 and *all* other characters. 555?? = 555 and *any 2* characters So: 5556 will match 555* 55566 will match 555?? A10 = 5556: =VLOOKUP(LEFT(A10,3)&"*",list1,2,0) A11 = 55566: =VLOOKUP(LEFT(A11,3)&"??",list1,2,0) -- Biff Microsoft Excel MVP "lutherdail" wrote in message ... list one col a col b 555* xyz 666?? abc List two 5556 5557 66622 I would like a formula which would look up the items in list two, search list one (which contains wildcard characters) and return the correct matching item. example: I would like to look up 5556 in list a and return xyz and look up 66622 and return abc. I can easily do in reverse, but I can't figure out how to do in this direction with the wildcards in the list a |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
looking up items in a list with wildcards
not sure if this will help any, but where the value to look up is in D1, this
*appears* to work as long as the number in D1 is formatted as text (I couldn't nest TEXT in the match function and get it to work): array entered: =LOOKUP(MIN(IF(ISNUMBER(MATCH(A$1:A$2,D1,0)),ROW(A $1:A$2))),ROW(A$1:A$2),B$1:B$2) Alternatively, maybe he could use a UDF (as long as the wildcard patterns in his table mirror those used by the vba like operator): Option Compare Text Option Explicit Function WildMatch(strData As String, rngCriteria As Range) Dim lngCount As Long For lngCount = 1 To rngCriteria.Columns(1).Cells.Count If strData Like rngCriteria.Columns(1).Cells(lngCount).Value Then WildMatch = lngCount Exit Function End If Next lngCount WildMatch = CVErr(xlErrNA) End Function Then use: =INDEX(B1:B4, wildmatch(D1, A1:A4)) "T. Valko" wrote: You can use a single formula: =VLOOKUP(LEFT(A10,3)&IF(LEN(A10)=4,"*","??"),list1 ,2,0) If the lookup_value is 4 characters then it uses the "*" wildcard, otherwise it uses the "??" wildcards. So, I'm assuming all lookup_values are either 4 or 5 characters. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... This sounds like a nightmare! Wildcards don't work on numbers. Your list one starts with 3 digits. You *might* be able to use something like this: 555* = 555 and *all* other characters. 555?? = 555 and *any 2* characters So: 5556 will match 555* 55566 will match 555?? A10 = 5556: =VLOOKUP(LEFT(A10,3)&"*",list1,2,0) A11 = 55566: =VLOOKUP(LEFT(A11,3)&"??",list1,2,0) -- Biff Microsoft Excel MVP "lutherdail" wrote in message ... list one col a col b 555* xyz 666?? abc List two 5556 5557 66622 I would like a formula which would look up the items in list two, search list one (which contains wildcard characters) and return the correct matching item. example: I would like to look up 5556 in list a and return xyz and look up 66622 and return abc. I can easily do in reverse, but I can't figure out how to do in this direction with the wildcards in the list a |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
looking up items in a list with wildcards
On Feb 14, 9:26 pm, lutherdail
wrote: list one col a col b 555* xyz 666?? abc List two 5556 5557 66622 I would like a formula which would look up the items in list two, search list one (which contains wildcard characters) and return the correct matching item. example: I would like to look up 5556 in list a and return xyz and look up 66622 and return abc. I can easily do in reverse, but I can't figure out how to do in this direction with the wildcards in the list a The tilde ~ will mask wild card characters - they will be interpreted as ordinary characters change 555* to 555~*. use translate function. I hope this helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching items in 2 list to consolidate to one list | Excel Discussion (Misc queries) | |||
group items in a pivot table using wildcards | Excel Discussion (Misc queries) | |||
last 20 items in a list | Excel Worksheet Functions | |||
How do I delete items in one list from another list? | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |