looking up items in a list with wildcards

[lutherdail]

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

 

[T. Valko]

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)

 

[T. Valko]

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.

 

[JMB]

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))

 

[ed.dumas.1]

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.