Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 21 to 30 of 47

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

  1. #21
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider


    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.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #22
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    I think this ARRAY formula does what you want

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

  3. #23
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 by Ken Puls; 2011-07-13 at 07:23 PM. Reason: Tried to post corrected formula with NOPARSE tags, but still displayed extra characters.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #24
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Ken Puls View Post
    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. #25
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Bob Phillips View Post
    Ken, As you well know, there is no money and little recognition in writing a book.
    LOL! True enough.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #26
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #27
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Hi Bob, why the coercion with the double unary?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  8. #28
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Simon Lloyd View Post
    "I don't think it can be done..."
    LOL! EVERY time I say this someone comes along and proves otherwise.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #29
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Simon Lloyd View Post
    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. #30
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Bob Phillips View Post
    ......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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast

Tags for this Thread

Posting Permissions

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