Page 1 of 5 1 2 3 ... LastLast
Results 1 to 10 of 47

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

  1. #1

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



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

    I'm doing some work on analysing website search terms. I have:

    - one worksheet, search_terms, containing all the search terms for a given month
    - one worksheet, author_names, containing a list of author names.

    I want to add a column to search_terms that sets a flag whenever a name from author_names has been used in search_terms. However, I can only seem to get this working - with vLookup - for an exact match. It's not flagging rows in which, for example, an author name appears along with a subject keyword. So, "Joe Bloggs" returns a 1; "Joe Bloggs cheese" doesn't.

    Is there a way to do this, either using vLookup or using something else? I've tried a couple of approaches using wildcards, but I can only seem to get it to work in the other direction (i.e. when the data in search_terms is a subset of the data in author_names).

    Many thanks,

    Rachael

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    You can use wildcards like this:
    =VLOOKUP("*"&C1&"*",author_names!A1:B100,1,FALSE)
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    I've tried that, but it doesn't seem to be working. E.g. I have an entry "joanne bloggs, operation" in search_terms, and "Joanne Bloggs" in author_names, but the vLookup isn't matching those two.

    Does it have to match case exactly?

    I'm using Excel 2011 on Mac OSX 10.6.

  4. #4
    I've just tried lowercasing all the author names in the author_names worksheet, and it still doesn't work - matching fine when the author name is the only thing in the cell, not when there's any other text involved.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum!

    I'm curious, are you trying to look up "Joanne Bloggs" in a table containing "joanne blogs, operation", or trying to look up "joanne bloggs, operation" in a table containing "Joanne Bloggs"?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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. #6
    The first - sorry if that wasn't clear from the OP.

    I have one sheet with a list of author names, and one with a list of search terms which may or may not include the author names. I want to flag the ones that do, but the vLookup is only finding exact matches.

    I suppose I could turn it the other way round and look up my search terms in the author names table, see if that will find the matches, but it's a larger table, and I don't really want to sort the search terms alphabetically (I guess I could do it temporarily).

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    No, don't turn it around. That's would make it MUCH harder. To look up the author name in the search terms table should be a piece of cake.

    Have a look at the attached file and see if that does what you're after.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  8. #8
    This is working, but the information is the wrong way round (so the "MUCH harder" you refer to...) compared to how I have it set up. My 'table' is the author names and the lookup terms are the search terms.

    So it sounds like I have it back to front...

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Yes... the issue is that when you search using vlookup, it needs to find the whole string you pass to the function. So the only route you really have here is to work up some formulas to trim down the parameter that you're passing it. Out of curiosity, are your search terms always Author first, followed by a comma, then by more terms? If so you could write a formula to only take the characters up to the first comma...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Here, try this one. You should be able to search for "Joanne Bloggs, Operation" or "Joanne Bloggs" and return a result. The key is that the author name must be first in the query string though.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

Page 1 of 5 1 2 3 ... 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
  •