Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 31 to 40 of 47

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

  1. #31


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

    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. #32
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    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. #33
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    You could also use

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

  4. #34
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    Just for the hell of it, a non-array, SUMPRODUCT solution

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

  5. #35
    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
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #36
    Quote Originally Posted by Simon Lloyd View Post
    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. #37
    Neophyte chart68's Avatar
    Join Date
    Jan 2015
    Location
    Reading UK
    Posts
    4
    Articles
    0

    Smile Fantastic but..

    Quote Originally Posted by Bob Phillips View Post
    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. #38
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    Just put the formula in column B?

  9. #39
    Neophyte chart68's Avatar
    Join Date
    Jan 2015
    Location
    Reading UK
    Posts
    4
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    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. #40
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    Claire, that VLOOKUP should work if I understand the data correctly. Give an example where it doesn't work.

Page 4 of 5 FirstFirst ... 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
  •