+ Reply to Thread
Results 1 to 12 of 12

Lookup Partial String Match In Excel

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    Slovenia
    MS-Off Ver
    Office 2016
    Posts
    5

    Lookup Partial String Match In Excel

    I am using Vlookup to link pais of value, where I am trying to link values not by exact values, but based on partual value. Similar to what is described here: exceljet.net/formula/partial-match-with-vlookup, but in opposite way.
    In this example "Aya" is linked with the value "Ayala", while in my case I would like to achieve that my vlookup will recognize "Ayala" and connect it with "Aya" in my reference table.
    How to achieve that?
    I though I should replace:
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    , but it is not working

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    887

    Re: Lookup Partial String Match In Excel

    Change FALSE to TRUE

    Please Login or Register  to view this content.
    Make sure your data is sorted Ascending as the data will read top to bottom and find the first Likeness...

    Now if you know you want to match say off the first 3 char and then anything after you could get the LEFT(Value,3) for the lookup value
    -If you think you are done, Start over - ELeGault

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    Slovenia
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Lookup Partial String Match In Excel

    Hey ELeGault,
    Thank you for proposed solution. At first, I thought it is working what I am expecting, but than I did some initial testing and I am afraid it is not. I have a problem, when the value is not exactly the same. In that case it link it with the closest value. In above example If I will enter "Ayola" it will link it with "Aya", since it is the closes value, while I would like to get N/A for a strings that are not included in the initial string. Is it possible to catch such a cases?
    Thank you in advance,
    Rok

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,469

    Re: Lookup Partial String Match In Excel

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,469

    Re: Lookup Partial String Match In Excel

    Any chance you could upload a workbook, please?

  6. #6
    Registered User
    Join Date
    06-23-2022
    Location
    Slovenia
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Lookup Partial String Match In Excel

    Example is attached. In the first table it is shown How current formula is working. In the second it is shown, how I am expecting it to be.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,809

    Re: Lookup Partial String Match In Excel

    Try

    =IFERROR(INDEX(Table4[Value],MATCH(LEFT(D11,3),Table4[String],0)),"")
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,987

    Re: Lookup Partial String Match In Excel

    Try this:

    =IFERROR(1/(1/SUM(IF(ISNUMBER(SEARCH(Table4[String],D11)),Table4[Value],""))),"")

    it works fine in O365. In your version of Excel it may be an array formulla, and will need CTRL-SHIFT-Enter to work.
    Attached Files Attached Files
    Glenn



  9. #9
    Registered User
    Join Date
    06-23-2022
    Location
    Slovenia
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Lookup Partial String Match In Excel

    Glenn Kennedy,
    Thank you very much. It is working exactly in a way...I wanted it in my example. But in my real case....values I would like to link to, are not values, but strings. In that case, formula is not working. Most probably it has something to do with "ISNUMBER" check...but I am not able to change it in a right way. I am attaching another example with string. How to change additional formula in a way it will work also for this case?
    Attached Files Attached Files

  10. #10
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,020

    Re: Lookup Partial String Match In Excel

    Cell E11 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell F11 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-23-2022
    Location
    Slovenia
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Lookup Partial String Match In Excel

    Hm, I am not sure, this is working as I am expecting. As I can see it is checking only the first 3 characters. I would like to check dynamically for any part of the string.
    In the initialy formula, prapred by Glenn it was working...the only problem there was, that it was linking it to the numberic value which is not the case in my scenario.
    Last edited by AliGW; Today at 04:58 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,987

    Re: Lookup Partial String Match In Excel

    1. Make sure your sample sheets represent what you REALLY have (text for text, numbers for numbers).

    2. Do NOT extend the lookup table(col I&J) to include blank cells. The table will extend as needed ITSELF.

    3. Use this (ENTER to set):

    =IFERROR(LOOKUP(1000,SEARCH(Table4[String],A11),Table4[Value]),"")
    Attached Files Attached Files

+ 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] Excel Match + Capture partial text string
    By dbrownla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2021, 09:37 AM
  2. Lookup if Partial String match exists
    By G.Bregvadze in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2018, 04:30 AM
  3. Lookup if Partial String match exists
    By G.Bregvadze in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2018, 04:27 AM
  4. [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
  5. Partial lookup from text string, return value from lookup list
    By TotallyLost in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-13-2016, 12:14 PM
  6. [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
  7. Complex matching with indirect lookup based on partial string match
    By jdcb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-17-2012, 08:19 AM

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