VLOOKUP wildcard woes

[basicallyawesome]

Hi, I am having the worst time with a basic VLOOKUP. Ive googled and I really feel like it should work but… it doesnt.
My main sheet has a list of birds, some specified “barn owl”, some not. My comparison sheet has broad groupings (e.g., owl)
My VLOOKUP in B2 on my main sheet looks like

=VLOOKUP("*"&A2,Sheet2!A1:D350,2,FALSE)

Everything I’ve read says the cells with barn owl should return results because ofthe Wildcard in the formula and owl in sheet 2. However, it is returning #N/A. All others that are exact match are returning results. This is a problem, because I’m trying to catch misses caused by our current VLOOKUP which just finds exact matches, which can and have resulted in a contract breach. Any ideas how I can fix this?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this make any difference

=VLOOKUP("*"&A2&"*",Sheet2!A1:B350,2,FALSE)

 

[Peter_SSs]

With your owl/barn owl example,

1. What exactly is in cell A2 of your main sheet?
2. What exactly is in column A of Sheet2 that you think should match if the wildcard was working?

Also be aware that if you are trying to match bird names using a short name like "owl" & using wildcards, you might accidentally match birds like
black-cowled oriole
owlet-nightjar
guineafowl
meadowlark
greater yellowlegs
lowland akalat

 

[RoryA]

Everything I’ve read says the cells with barn owl should return results because ofthe Wildcard in the formula and owl in sheet 2

If A2 contains "barn owl" your formula is looking for anything in sheet2 column A that ends with "barn owl"

 

[basicallyawesome]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this make any difference

=VLOOKUP("*"&A2&"*",Sheet2!A1:B350,2,FALSE)

Thank you for the suggestion, I’ve updated my account details.

I realized yesterday when I was at work having forgot to check in here for responses and unable at that point to reply to any, that the formula was doing what it was designed to do… which wasn’t what I wanted it to. I did find this solution in the forum, which despite dating back over a decade, almost does what I need (E.g., turns up results whether barn or owl), however, I realized I also need barn-owl which the above doesn’t catch, but at least I’m closer.

 

[basicallyawesome]

If A2 contains "barn owl" your formula is looking for anything in sheet2 column A that ends with "barn owl"

You are correct, I realized that yesterday reproducing the spreadsheet on the site I found that solution on.

i did find this, which almost does what I want, but doesn’t turn up barn-owl (with the dash presumably being the problem).

 

[RoryA]

Yes, that kind of relates to Peter's question. Is a hyphen the only "special" character that might be involved?

 

[basicallyawesome]

Yes, that kind of relates to Peter's question. Is a hyphen the only "special" character that might be involved?

Whoops! Missed Peter’s question entirely. Yes, hyphen is the only “special” character that might be involved.

 

[Peter_SSs]

Whoops! Missed Peter’s question entirely.

.. and still not answered it.

With your owl/barn owl example,

1. What exactly is in cell A2 of your main sheet?
2. What exactly is in column A of Sheet2 that you think should match if the wildcard was working?

Better still, could you give us a smallish set of sample data from both sheets, including examples with "-", (and include the expected results) with XL2BB so that we can do some testing with your data?

 

[RoryA]

Yes, hyphen is the only “special” character that might be involved

It would still be preferable to have a representative data sample, but perhaps a small amendment to the formula Aladin posted in the thread you linked to - something like:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10&" "," "&SUBSTITUTE(A2,"-"," ")&" "),$E$2:$E$10)