# Partial Match Cell To String Array

1. ## 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?

2. ## 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

3. ## 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. ## 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. ## Re: Partial Match Cell To String Array

Cool.

Well, thanks for you help!

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

#### 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