# Thread: vLookup with wildcards (equivalent of "contains") - best solution?

Thanks, all, for this, and apologies for delay in responding - a combination of out of the office and then not being able to post on the board...

This works 'in the wild' for me. The only thing is that instead of just a 1 or a 0, I get numbers through to 6. Is the formula also counting the total number of occurrences, or is there something else going on here?

2. This could happen if you have multiples. Try this array version instead

=--(SUM(IFERROR(MATCH("*"&author_names!\$A\$2:\$A\$7&"*",A2,0),0))>0)

(Simon, I recall the -- now, this is what I originally tried)

3. You could also use

=MAX(IFERROR(MATCH("*"&author_names!\$A\$2:\$A\$7&"*",\$A2,0),0))

4. Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!\$A\$2:\$A\$7&"*",\$A2,0))))>0)

5. Originally Posted by Bob Phillips
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!\$A\$2:\$A\$7&"*",\$A2,0))))>0)
And there we have it , knew you couldn't resist, some may call it predictable, i choose to call it dependable

6. Originally Posted by Simon Lloyd
And there we have it , knew you couldn't resist, some may call it predictable, i choose to call it dependable
I've been hunting around for a solution for sometime now and stumbled across this forum.

Just signed up to say thank you and I shall be using this website some more when I need a solution!

Liam

7. ## Fantastic but..

Originally Posted by Bob Phillips
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!\$A\$2:\$A\$7&"*",\$A2,0))))>0)
Hello I've just found this & I love it, it's worked out my match, but I once I've matched the value in col A I want to output the value in column B, any help would be extremely appreciated.

8. Just put the formula in column B?

9. Originally Posted by Bob Phillips
Just put the formula in column B?
Sorry I didn't make myself very clear, I have column A & B in sheet 2 and I need to look up the value of column B from that sheet 2 which matches the value in column A from the sheet 1.

I'm looking up from

=VLOOKUP("*"&A2&"*",Sheet2!\$A\$2:\$B\$100,2,FALSE) this won't work as the wildcard doesn't seem to match the data as per =--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!\$A\$2:\$A\$100&"*",\$A2,0))))>0) how do I get these to work together.

Thanks

Claire

10. Claire, that VLOOKUP should work if I understand the data correctly. Give an example where it doesn't work.

Page 4 of 5 First ... 2 3 4 5 Last