Search code examples
excel-formulaexcel-2013

search and return multiple values in excel

I have searched for a solution to this all morning with no luck.

I am looking for a formula that will find certain words in one column and pull them out into an adjacent column.

For example Column E2 is "Greenwood Hobby" while E3 is "Dayton Lifestyle" - I need just the location to be pulled out into a separate column so I don't have to use text filters in my data when I place into a pivot table.

I have tried combining some formulas to no avail.

=IF(ISERROR(SEARCH({"Carmel","Greenwood","Dayton","Florence"},E2,1)),"","{"Carmel","Greenwood","Dayton","Florence"})

This formula only displays the first value "Carmel" instead of each value as it relates to the searched cell.

I'd like my cells to appear like this:

E2               | F2
Carmel Lifestyle |  Carmel
Dayton Core      | Dayton

And so on....

Is there a way to search for multiple text variables and return that in a separate column?

Solution

  • So you can put the list of desired outcomes in a range and refer to that, I put them in J2:J5 and used the following array Formula:

    =INDEX($J$2:$J$5,MATCH(1,IF(ISNUMBER(SEARCH($J$2:$J$5,E2)),1,0),0))

    Being an Array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done Properly Excel will put {} around the formula.

    enter image description here