+ Reply to Thread
Results 1 to 4 of 4

Simple search for text from list, return adjacent text - match / vlookup / index?

  1. #1
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Simple search for text from list, return adjacent text - match / vlookup / index?

    Vacuum example.xlsx

    Hi, this should be simple.... only I'm struggling!

    I have sheet 1 with a row of manufacturer names in column A

    Hoover International
    Hoover UK
    Numatic
    Numatic International
    Bosch GB
    Bosch EU
    BSCH (INT)
    NMTC


    In sheet 2 I have the search terms and the results in A & B

    A : B
    Hoover : Hoover
    Bosch : Bosch
    BSCH : BOSCH
    NMTC : NUMATIC
    Numatic : NUMATIC

    I would like a search of sheet 1 with the text in sheet 2. If there's a match, column B will be put next to the text in culumn A like:
    A : B
    Hoover International: HOOVER
    Hoover UK : HOOVER
    Numatic : NUMATIC
    Numatic International: NUMATIC
    Bosch GB : BOSCH
    Bosch EU : BOSCH
    BSCH (INT) : BOSCH
    NMTC : NUMATIC

    Thanks!
    Last edited by aceparts; 06-17-2013 at 12:21 PM.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Simple search for text from list, return adjacent text - match / vlookup / index?

    Hi,

    In cell B2 of Sheet1 enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) and copy down:

    =INDEX(Sheet2!$B$1:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A$5,Sheet1!A2)),0))

    NOTE: You currently have extra spaces in some of your entries in Sheet2 which will mean that the above will not match for these cases, e.g. in Sheet1 column A "NMTC" but in Sheet2 column A "NMTC ". If you require a refinement to the above formula to take into account potential inexact matches, please let me know. Otherwise, I will assume it was an error on your part.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Simple search for text from list, return adjacent text - match / vlookup / index?

    Brilliant - SOLVED! Many many thanks!

    Alan

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Simple search for text from list, return adjacent text - match / vlookup / index?

    ни за что.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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