+ Reply to Thread
Results 1 to 5 of 5

Partial Match Cell To String Array

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Partial Match Cell To String Array

    Morning all,

    I might struggle to explain this one, so please bear with me if the question is unclear.

    I'll elaborate below, but this is the end result I want to see:

    A B C
    Dark Green Box RED GREEN
    Yellow Flower Pot YELLOW YELLOW
    Gold Coin GREEN GOLD
    Blue Fish BLUE BLUE
    Cured Ham BLACK RED
    WHITE
    GOLD
    SILVER

    Columns A and B are populated by me, I want a formula in column C to look at the contents of column A on the same row, and see if anything in all of column B matches. If it does, I want it to return the contents of column B.

    In reality, column B is in a separate worksheet, but I assume the formula is much the same even if the columns are right next to each other.

    Any ideas on how this can work?
    Last edited by amazinglazers; 08-09-2013 at 05:56 AM.

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

    Re: Partial Match Cell To String Array

    Hi,

    Assuming that the table you give is in the range A2:C9, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in C2 and copy down as required:

    =IFERROR(INDEX($B$2:$B$9,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$9,A2)),0)),"")

    You don't mention (or give examples of) cases in which more than one of the entries in column B is present in an entry in column A, so I assume that this is never the case.

    Regards
    Click * below if this answer helped

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

  3. #3
    Registered User
    Join Date
    06-21-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Partial Match Cell To String Array

    Works perfectly, thank you.

    Multiple matches are not a concern in my case, but I would be interested to know how that problem could be dealt with.

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

    Re: Partial Match Cell To String Array

    You're welcome.

    In the case of more than one match, the best approach would be to allow one cell for each match, e.g. extended in columns to the right. In that case the formula (again, array-entered) in C2 would become:

    =IFERROR(INDEX($B$2:$B$9,SMALL(IF(ISNUMBER(SEARCH($B$2:$B$9,$A2)),ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1),COLUMNS($A:A))),"")

    for which, e.g. "Gold and Black and Silver and Red Coin" in cell A4 would return, based on your list in column B, "RED", "BLACK", "GOLD" and "SILVER" in cells C4, D4, E4 and F4 respectively.

    Concatenating all matching results into a single cell is a different matter and one which would require a VBA-based solution.

    Regards

  5. #5
    Registered User
    Join Date
    06-21-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Partial Match Cell To String Array

    Cool.

    Well, thanks for you help!

+ 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. Trying to match partial string with another string and give position or cell
    By pat3white in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-01-2013, 10:07 AM
  2. [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
  3. [SOLVED] Partial string match with file name
    By jamer02 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2012, 06:50 AM
  4. Partial String Match
    By Brittney10 in forum Excel General
    Replies: 2
    Last Post: 07-18-2010, 04:04 PM
  5. Partial String Match Using VLOOKUP
    By cdhmotes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-26-2005, 06:30 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