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

I don't think Bob would disagree, Simon. I didn't say it couldn't be done, just that I couldn't figure it out. ;) (I fully expect Bob would shake his head at me and bang it off without even needing to test it.)
 
I think this ARRAY formula does what you want

=--SUM(IFERROR(MATCH("*"&author_names!$A$2:$A$6&"*",A2,0),0))
 
Thanks Bob. I dropped it in and Excel corrected it (I think the board picked up an extra space in there between the A and 2.)

Flossie, to commit the Array formula, enter the formula then press CTRL+SHFT+ENTER. Then copy it down to the other cells.

Bob, one day I need you to write a book on working with array formulae. ;)
 
Last edited:
Ken Puls said:
Bob, one day I need you to write a book on working with array formulae. ;)
Click to expand...

Ken, As you well know, there is no money and little recognition in writing a book.
 
Besides which Ken Bob IS the book :), you know, i wish fishing was as easy as getting Bob to answer a formula question, just throw him the "I don't think it can be done..." and it's like a red rag to a bull ;)
 
Simon Lloyd said:
Hi Bob, why the coercion with the double unary?
Click to expand...
Do you know, I was wondering that myself. It is certainly not necessary, probably I ised it in s first attempt and didn't remove it.
 
Bob Phillips said:
......probably I ised it in s first attempt and didn't remove it.
Click to expand...
Nice to see you are human after all and that it sometimes does take you more than one go ;)

Actually i thought it was a "best kept secret" of manipulating SUM, so i thought there was going to pearls cast before me :D
 
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?
 
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)
 
You could also use

=MAX(IFERROR(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0),0))
 
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0))))>0)
 
Bob Phillips said:
Just for the hell of it, a non-array, SUMPRODUCT solution

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

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
 
Fantastic but..

Bob Phillips said:
Just for the hell of it, a non-array, SUMPRODUCT solution

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

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.
 
Just put the formula in column B?
 
Bob Phillips said:
Just put the formula in column B?
Click to expand...

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
 
Claire, that VLOOKUP should work if I understand the data correctly. Give an example where it doesn't work.
 
You must log in or register to reply here.
Back
Top