PDA

View Full Version : [SOLVED] Vlookup: Wildcard in Reverse



nameuser321
08-26-2013, 01:02 PM
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
08-27-2013, 07:55 AM
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
08-27-2013, 08:24 AM
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
08-27-2013, 11:11 AM
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
08-27-2013, 11:52 AM
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
08-27-2013, 12:45 PM
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
08-27-2013, 12:48 PM
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
08-28-2013, 08:05 AM
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