+ Reply to Thread
Results 1 to 10 of 10

Vlookup with wildcards not working? Search cell for partial text match and return value

  1. #1
    Registered User
    Join Date
    07-07-2014
    Location
    midwest
    MS-Off Ver
    2013
    Posts
    24

    Vlookup with wildcards not working? Search cell for partial text match and return value

    Hi,

    In the enclosed sheet am trying to use the formula to search a number of entries that contain data and return a value if a partial match is found from a reference data set with a specific value. I have seen others use wildcards to search inside a string, but it does not seem to work for me. Perhaps a better way to do it?

    =VLOOKUP("*"&A2&"*",$A$9:$B$13,2,FALSE)

    If I just use the data I am looking for it works fine, just not along with other text.

    Notes -
    The notation I am searching for will always be at the end of the string surrounded by (). Not sure if that makes is better or worse....



    Thanks in advance for any/all help.
    Attached Files Attached Files
    Last edited by Warder; 09-05-2017 at 10:16 AM. Reason: clarity

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    Hi,

    That won't work because you actually need the wildcards applied to the lookup table values, not the lookup value. You might try this
    =INDEX($B$9:$B$13,MATCH(1,INDEX(COUNTIF(A2,"*"&$A$9:$A$13&"*"),),0))
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    OR,

    Try the following:

    In C1:

    =VLOOKUP(MID(A2,SEARCH("(",A2)+1,3),$A$9:$B$13,2,0)

  4. #4
    Registered User
    Join Date
    07-07-2014
    Location
    midwest
    MS-Off Ver
    2013
    Posts
    24

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    @xlnitwit

    Ah! That seemed to work with my test data. I will apply to and report back.

    Thanks you so much!

  5. #5
    Registered User
    Join Date
    07-07-2014
    Location
    midwest
    MS-Off Ver
    2013
    Posts
    24

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    Quote Originally Posted by cbatrody View Post
    OR,

    Try the following:

    In C1:

    =VLOOKUP(MID(A2,SEARCH("(",A2)+1,3),$A$9:$B$13,2,0)
    Thank you sir - I will play with this one as well.

  6. #6
    Registered User
    Join Date
    09-05-2017
    Location
    new delhi
    MS-Off Ver
    2013
    Posts
    21

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    hiii

    use this simple formulla well working. i have tested.

    =VLOOKUP(MID(A2,FIND("(",A2)+1,3),$A$9:$B$13,2,FALSE)

    hope your query is solved...
    if yes plz mark this as Solved

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Registered User
    Join Date
    07-07-2014
    Location
    midwest
    MS-Off Ver
    2013
    Posts
    24

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    Thank you all for helping.... one wrinkle that makes most of these not work is that my data (AA1) can sometimes be only two characters in length causing those to fail, i.e. (X3). The values inside the () is unique if that helps.

    I could not get the index/match function to work with my data.

    Thanks again - I will keep trying.

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    This should solve your problem
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 09-05-2017 at 11:21 AM.

  10. #10
    Registered User
    Join Date
    07-07-2014
    Location
    midwest
    MS-Off Ver
    2013
    Posts
    24

    Re: Vlookup with wildcards not working? Search cell for partial text match and return val

    Quote Originally Posted by AlKey View Post
    This should solve your problem
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    @ alkey

    Thank you - so that seems to be working! It is basically looking for any value within the () correct. 50 is the amount of characters to look within?

    Thanks to all who responded and helped!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup partial values and if match return partial value from another cell
    By Renejorgensen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-01-2017, 07:53 AM
  2. [SOLVED] VLOOKUP HELP! Search for partial text
    By sls1915 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-24-2015, 03:48 PM
  3. [SOLVED] vlookup partial match multiple return
    By Addisonb39 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2015, 12:10 PM
  4. Search text with wildcards in a table and return result as list in other sheet
    By nedned in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2015, 03:08 PM
  5. Vlookup True not working - need partial string match and returned values
    By leishab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2013, 04:44 PM
  6. Replies: 3
    Last Post: 06-17-2013, 01:09 PM
  7. [SOLVED] Vlookup, Match (Search or Find) partial string within string in a Cell
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 12:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1