CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Carol.Kar
Carol.Kar

Reputation: 5345

Search for list of values in string

I want to search for several values in a text string and return the wanted value.

I tried the following:

=IF(COUNTIF($E$3:$E$14;"*"&A2&"*")>0;"found";"not")

However, within search I only can input one search value.

My sheet looks like the following:

enter image description here

Any suggestions, how to give the search function multiple values?

I appreciate your replies!

Upvotes: 0

Views: 67

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use the following array formula:

=IFERROR(INDEX($E$3:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$3:$E$7,A2)),0)),"Not Found")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done properly then Excel will put {} around the formula.

enter image description here

You will need to replace the , with ; for your local requirements:

=IFERROR(INDEX($E$3:$E$7;MATCH(TRUE;ISNUMBER(SEARCH($E$3:$E$7;A2));0));"Not Found")

Upvotes: 3

Related Questions