Search for multiple strings in multiple columns

Question

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:

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!

In order of priority, does priority go to the order that the list in G is found or in which column the match is found? For example in your picture row2 has both test1 and return. Is test1 the correct answer becuase it is in a column to the left of return or because test1 comes before return on the list? — Scott Craner
– Scott Craner, Commented Sep 12, 2016 at 18:30
Change the A2 reference to a concatenation of the values A2 & B2 & C2 This will return the first value on the list found in one of the three columns. — Scott Craner
– Scott Craner, Commented Sep 12, 2016 at 19:35

Scott Craner · Accepted Answer · 2016-09-12 21:14:52Z

2

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.

answered Sep 12, 2016 at 21:14

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

I am on my way home tonight if I get time I will post an edit with the priority to the columns answer.

Drew D · Accepted Answer · 2016-09-12 21:12:25Z

1

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

answered Sep 12, 2016 at 21:12

Drew D

1271 silver badge6 bronze badges