CODEWITHSUNDEEP
excelexcel-formula
Carol.Kar
Carol.Kar

Reputation: 5355

Search for multiple strings in multiple columns

I want to search for several values in several columns, with text strings and return the wanted value.

I tried the following, which gives me for one column an indication of a string:

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

My sheet looks like the following:

enter image description here

The column Tried... only matches Match 3, hence I only get return back.

I was thinking of making a large if-else statement, however is there a smart way to search for multiple values in multiple columns?

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

I appreciate your replies!

Upvotes: 1

Views: 1344

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Just concantenate the three columns:

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

This is an array formula and must be confirmed with Ctrl-Shift-Enter.

Upvotes: 2

Drew D
Drew D

Reputation: 127

A bit of legwork but you could do an IFERROR(IF(AND([Column 1], [Column 2])))

Upvotes: 1

Related Questions