Vlookup: Wildcard in Reverse

[nameuser321]

I understand that you can put a wildcard in your vlookup formula like this =VLOOKUP("Apple*", J25, 1, FALSE) but can you do a vlookup on a table that contains a bunch of values with wildcards and have those wildcards work as wildcards? For example I have a table such as the one below. I want to us a vlookup =VLOOKUP("F370401:2309:61N", table, 3, FALSE) and have it return F410T19. Is this possible? Is there an easier way? I'm trying to avoid using VBA if possible. Any help is much appreciated.

Logic String	New Site	Region	F-Code
F370301:*:*			F410T12
F370401:*:*			F410T19
F378001:*:*			F410T14
F380101:*:*			F410T11

[shrivallabha]

Assuming that you have F370401:2309:61N in cell A1, does the following work:
=VLOOKUP(MID(A1,1,FIND(":",A1,1)-1)&"*",table,4,0)
Looking at your layout above F-Code is 4th column.

Or alternatively if table data is in say D1:G5 (and string in A1 as above) then you may want to try:
=LOOKUP(99,SEARCH(D1: G5,A1,1),G1:G5)
Note: There is space between ":" & "G5" in second formula as it otherwise converts to smiley.

[nameuser321]

Unfortunately this does not work for me. I didn't include all possible exceptions on the "exception report". If I use the first formula and enter F370401:2309:61N with this extended table (two additional entries) it should match 2 patterns (the last and second to last), which the vlookup formula I believe should defer to the first. The result of the formula you have given me returns the second f-code F410T19. Thanks for the assistance, do you know of another solution?

Logic String	New Site	Region	F-Code
F370301:*:*			F410T12
F370401:*:61B			F410T19
F378001:*:*			F410T14
F380101:*:*			F410T11
F370401:*:61N			F410T18
F370401:*:*			F410T1S

[shrivallabha]

Thanks for the example.

Try [CTRL+SHIFT+ENTER and not just ENTER]:
=INDEX(E3:E8,MATCH(MAX(IF(ISNUMBER(SEARCH(B3:B8,H2,1)),LEN(B3:B8))),IF(ISNU MBER(SEARCH(B3:B8,H2,1)),LEN(B3:B8)),0))
If entered correctly Excel will surround it with braces.

[david000]

I tried this with Advanced Filter and got the both results you where looking for, so it may be a workable option.

Original String:

F370401:2309:61N

Advanced filter String:
F370401:~*:61*

[nameuser321]

Thanks for the assistance shrivallabha! I originally thought that I wouldn't draw a distinction between the two last records but somehow you were able to give priority to the last. Trying to undestand what you did here. I'm farmiliar with the array formulas but I'm getting lost in the details.

[nameuser321]

Thanks david000 for your response, but I need this to be a formula that checks an "exception table" first. If it can't find the value in the exception table it should look up the value in a default table. Thanks for the replies!

[shrivallabha]

Thanks for the assistance shrivallabha! I originally thought that I wouldn't draw a distinction between the two last records but somehow you were able to give priority to the last. Trying to undestand what you did here. I'm farmiliar with the array formulas but I'm getting lost in the details.

-- SEARCH formula allows wildcards so we use it to check matching patterns. So basically following part:
ISNUMBER(SEARCH(B3:B8,H2,1))
will return TRUE if the pattern matches.

-- You wanted the value against the best wild card match which means the search item that has more characters matching so we check LENgth of each match given by above condition. So for every TRUE condition it returns:
LEN(B3:B8)

--Then by using MAX we find out the one which is bigger in length.
MAX(IF(ISNUMBER(SEARCH(B3:B8,H2,1)),LEN(B3:B8)))
e.g. as opposed to "F370401:*:*", "F370401:*:61N" has 2 more characters. If you put "F370401:2309:61N" in one of the cells then it will return MATCH against it.

--Then we search up where exactly is this is positioned using
MATCH(MAX_Length,Array of Lengths,0)

--And then INDEX/MATCH combo works as usual