Search an array for a text string in a separate array, return that text string (Excel)

Question

I have a column of text values (column C below), they are of varying length. I have a separate array of text values (column A below). I want a formula that will look through the strings in Column C and return the word from Column A that it matches. If there are multiple matches (rare, probably won't happen), it will only return one.

Please see my example:

Column D is the result I am looking for. As you can see, this will require some kind of fuzzy string matching. The word could appear anywhere in the cell. It doesn't need to be case sensitive. But I need it to pull that word out of the string as the result basically.

Thank you!

Scott Craner · Accepted Answer · 2017-10-10 19:53:01Z

7

Use This:

=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$5)/(ISNUMBER(SEARCH($A$2:$A$5,C2))),1))

answered Oct 10, 2017 at 19:53

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lorenzo Over a year ago

Perfection. Thank you so much!

TotsieMae Over a year ago

This is a beautiful solution! Can you please explain how your answer works? The AGGREGATE function, in particular, has always been a challenge for me and I don't personally know anyone who uses it. Is your formula simply returning the first value in the $A$2:$A$5 list that causes the division part of the formula to return a non-error value?

Pang · Accepted Answer · 2017-10-13 04:41:37Z

2

One more, slightly simpler:

=IF(COUNTIF($C$1:$C$100," * "&A2&" * ")>0,A2,"")

edited Oct 13, 2017 at 4:41

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Oct 12, 2017 at 20:32

Roger H.

3363 silver badges7 bronze badges

Comments

Kresimir L. · Accepted Answer · 2017-10-10 19:58:55Z

0

You can also try this formula =INDEX(A:A,SUMPRODUCT(MATCH(1,NOT(ISERR(SEARCH(A:A,C2)))*1,0))). Enter it using CTRL+SHIFT+ENTER since it is an array formula.

answered Oct 10, 2017 at 19:58

Kresimir L.

2,4412 gold badges12 silver badges22 bronze badges

2 Comments

Scott Craner Over a year ago

1. When using array formulas one should limit the sized of the referenced data to the data set to lessen the unneeded number of iterations. Currently your formulas are doing over 5 million calculations. 2. Your formula can be simplified to =INDEX($A$2:$A$5,MATCH(1,ISNUMBER(SEARCH($A$2:$A$5,C2))*1,0)) No need for SUMPRODUCT when using Ctrl-Shift-Enter.

Kresimir L. Over a year ago

Thanks you, it was more a matter of speed that I left search with whole column referenced...but for 2. definitively more elegant solution.

H. Patel · Accepted Answer · 2018-12-18 12:46:48Z

0

This formula seems to be working for me: (Please use Ctrl+Shift+Enter for the array formula to take effect)

{=INDEX($A$2:$A$5,MATCH(1,COUNTIF(C2,"*"&$A$2:$A$5&"*"),0))}

Screenshot of Excel sheet

edited Dec 18, 2018 at 12:46

answered Dec 17, 2018 at 16:02

H. Patel

12 bronze badges

Collectives™ on Stack Overflow

Search an array for a text string in a separate array, return that text string (Excel)

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related