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

1. Register for a FREE account, and/
or Log in to avoid these ads!

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.)

2. I think this ARRAY formula does what you want

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

3. 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.

4. Originally Posted by Ken Puls
Bob, one day I need you to write a book on working with array formulae.
Ken, As you well know, there is no money and little recognition in writing a book.

5. Originally Posted by Bob Phillips
Ken, As you well know, there is no money and little recognition in writing a book.
LOL! True enough.

6. 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

7. Hi Bob, why the coercion with the double unary?

8. Originally Posted by Simon Lloyd
"I don't think it can be done..."
LOL! EVERY time I say this someone comes along and proves otherwise.

9. Originally Posted by Simon Lloyd
Hi Bob, why the coercion with the double unary?
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.

10. Originally Posted by Bob Phillips
......probably I ised it in s first attempt and didn't remove it.
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

Page 3 of 5 First 1 2 3 4 5 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•