+ Reply to Thread
Results 1 to 9 of 9

VLOOKUP: Search string longer than target string

  1. 04-12-2017, 06:37 AM #1
    kophanz
    kophanz is offline
    Registered User
    Join Date
    11-20-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    20

    VLOOKUP: Search string longer than target string

    Hi,

    Thank you in advance for the help!

    In "Records" sheet, I have a list of units (Column A), and each unit has a corresponding score (Column B). In my actual document, there are 900+ units.

    In Sheet2, I have some units for which I want to find the corresponding score. The problem is that the name captured on the "Records" sheet are shortforms, so they are shorter than the actual name, which is used in Sheet2. As a result, the normal VLOOKUP doesn't work for me. Is there a way to make it work?

    [In the sample document, Sheet2 B2 should return '5', and Sheet2 B3 should return '7']

    I hope this is clear. Thank you very much!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-12-2017, 06:50 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: VLOOKUP: Search string longer than target string

    Try

    in B2

    =IF(A2="","",(INDEX(Records!$B$1:$B$7,SUMPRODUCT(--ISNUMBER(SEARCH(Records!$A$1:$A$7,Sheet2!A2))*ROW($A$1:$A$7)))))


    Copy down

    Corrected: no need for "array entry" (thank you Tony Valko)
    Last edited by JohnTopley; 04-12-2017 at 08:46 AM.
    Reply With QuoteRegister To Reply
  3. 04-12-2017, 07:04 AM #3
    southward
    southward is offline
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: VLOOKUP: Search string longer than target string

    Hi,

    I suggest a helper column in your Records sheet between your Name and Score column.

    Use an INDEX MATCH formula to return "Actual" names from Sheet2.

    In the Helper column use this array formula and copy down. Entered with CTRL/SHIFT/ENTER.

    Please Login or Register  to view this content.
    Now you can use a regular VLOOKUP in the Sheet2

    Attached is a version of your file showing how this works.

    Hope this makes sense and works for you.

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-12-2017, 07:11 AM #4
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: VLOOKUP: Search string longer than target string

    You can try this formula :
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 04-12-2017, 08:00 AM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP: Search string longer than target string

    Quote Originally Posted by JohnTopley View Post
    =IF(A2="","",(INDEX(Records!$B$1:$B$7,SUMPRODUCT(--ISNUMBER(SEARCH(Records!$A$1:$A$7,Sheet2!A2))*ROW($A$1:$A$7)))))

    ...confirmed by pressing CTRL+SHIFT+ENTER
    No need for array entry. Normal enter will do.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 04-12-2017, 10:41 PM #6
    kophanz
    kophanz is offline
    Registered User
    Join Date
    11-20-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    20

    Re: VLOOKUP: Search string longer than target string

    Quote Originally Posted by southward View Post
    Hi,

    I suggest a helper column in your Records sheet between your Name and Score column.

    Use an INDEX MATCH formula to return "Actual" names from Sheet2.

    In the Helper column use this array formula and copy down. Entered with CTRL/SHIFT/ENTER.

    Please Login or Register  to view this content.
    Now you can use a regular VLOOKUP in the Sheet2

    Attached is a version of your file showing how this works.

    Hope this makes sense and works for you.

    Cheers
    I've gone along with this. Very helpful!

    One issue I faced though. For some reason, in the helper column, the correct unit name did not appear. Can it be because some of the "shortform" names are not entirely shortforms? I have two examples below:

    Records Col A Sheet2 Col A
    The Nohumna Unit (NU) The Nohumna Unit, Sinpopine (NU)
    Likpo of Malme The Likpo of Malme
    Reply With QuoteRegister To Reply
  7. 04-13-2017, 01:50 AM #7
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,535

    Re: VLOOKUP: Search string longer than target string

    Testing Southward's it formula picks up the second instance, Likpo of Malme, without modification once the range is expanded. As to the first instance where the abbreviation is included in parentheses, the following modification to the array entered formula finds that one:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Note: Assumes the data is appended to the "A" columns in the file attached to post #3.
    If there are other types of anomalies in column A of the Records sheet then it might be worth reading the details of Fuzzy Lookup Add-In for Excel. Admittedly I have never used the add-in however the description of its abilities seems to be in line with your question.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  8. 04-13-2017, 05:56 AM #8
    kophanz
    kophanz is offline
    Registered User
    Join Date
    11-20-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    20

    Re: VLOOKUP: Search string longer than target string

    Problem solved!

    Thank you to everyone who chipped in. Much appreciated!
    Reply With QuoteRegister To Reply
  9. 04-13-2017, 08:38 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,535

    Re: VLOOKUP: Search string longer than target string

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Script to Search for a specific String then return cell values above that String-3
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 01:50 PM
  2. [SOLVED] Script to Search for a specific String then return cell values above that String-2
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 01:00 PM
  3. [SOLVED] Script to Search for a specific String then return cell values above that String
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 11:28 AM
  4. [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
  5. [SOLVED] Search for string across header row, then search for another string down found column
    By TucsonJack in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2012, 02:09 PM
  6. Vlookup of string within a longer string
    By aruna in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 10-28-2007, 02:18 PM
  7. search a string withing a string : find / search hangs
    By itarnak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-24-2005, 11:05 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1