Hi - simple enough - I am trying to do a wildcard match with a vlookup.
Formula :
=VLOOKUP("*"&A2&"*",code!A:B,2,0)
I am getting N/A for results.
Please help - see attachment. - jerryr0125
Hi - simple enough - I am trying to do a wildcard match with a vlookup.
Formula :
=VLOOKUP("*"&A2&"*",code!A:B,2,0)
I am getting N/A for results.
Please help - see attachment. - jerryr0125
Wildcards don't work quite like that I'm afraid.
"*"&A2&"*" in this instance means "any or no text here" & Elevator 456 & "any or no text here".
As Elevator 456 does not appear in column A of your Sheet2 either with or without text either side of it, it will always return an error.
BSB
Yes, the wildcard in VLOOKUP works the other way around from what you are thinking. In the example above it is looking for "*Elevator 456*" in column A of sheet code. I think you are trying to figure whether any of the words in column A of code can be found in A2 of desc.
This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.
=INDEX(code!$A$2:$A$4,SUMPRODUCT(ISNUMBER(SEARCH(code!$A$2:$A$4,A2))*ROW($1:$3)))
See attached.
Or try;
=LOOKUP(1,-SEARCH(code!$A$2:$A$4,desc!A2),code!$B$2:$B$4)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks