Search for multiple words in row and return searched value if found in first cell

Hello all,

I am in need to search a row for a term then return the value found to to a cell.

For instance
Search words
Apple
Banana
Strawberry
Pear
Watermelon

If any cell from B2 through E2 contains one of the search words then send that search term to B1. It would be nice if more than one was found it could show more than one result

I will have thousands of rows to search.

Unfortunately due to the sensitivity of the data I cannot upload a realistic view.
However I have made a file with 5 lines that would be similar to what we need.

Thank you.

Try this in A2:

=IFERROR(INDEX(B2:F2,SMALL(IF(ISNUMBER(SEARCH(G$11:G$15,B2:F2)),COLUMN(B2:F2)-1),1)),"") Ctrl Shift Enter

You haven't included the desired results so I'm guessing at what you want here.

Originally Posted by FlameRetired

Are you open to VBA? I surmise the multiple returns are to be concatenated in one cell?

Yes VBA would be fine. Multiple returns in one line would be nice to go into one cell.

Originally Posted by 63falcondude

Try this in A2:

=IFERROR(INDEX(B2:F2,SMALL(IF(ISNUMBER(SEARCH(G$11:G$15,B2:F2)),COLUMN(B2:F2)-1),1)),"") Ctrl Shift Enter

You haven't included the desired results so I'm guessing at what you want here.

This is pretty close. Thank you! The FIN came back with the whole term it found not sure if there is anything that can be done about that. It also did not show the multiple find in the same row. I am not even sure Excel can do this.

This return quite a bit and would already save about a day of work with just this.

I have tried to explain what is needed. I will think about how to explain it better where its understandable. However this is a HUGE help.

You can use a formula to return all of the search words found per row.

Try this:

H2 =IFERROR(INDEX($B2:$F2,SMALL(IF(ISNUMBER(SEARCH($G$11:$G$15,$B2:$F2)),COLUMN($B2:$F2)-1),COLUMNS($A:A))),"") Ctrl Shift Enter
Drag the formula to the right and down as far as needed.

See attachment.

I'll work on returning the search word, not the contents of the cell that the search word is in.

Originally Posted by whomewhy

This is pretty close. Thank you! The FIN came back with the whole term it found not sure if there is anything that can be done about that. It also did not show the multiple find in the same row. I am not even sure Excel can do this.

This return quite a bit and would already save about a day of work with just this.

I have tried to explain what is needed. I will think about how to explain it better where its understandable. However this is a HUGE help.

In my actual excel file it is returning the multiple, just not in the test. if it finds multiple it shows name1; name2

Originally Posted by FlameRetired

If you should ever decide to upgrade to Office 365 (subscription) it has the function TEXTJOIN ... which desktop 2016 does not ... which does the same thing .

I am actually using Office 365

Originally Posted by 63falcondude

You can use a formula to return all of the search words found per row.

Try this:

H2 =IFERROR(INDEX($B2:$F2,SMALL(IF(ISNUMBER(SEARCH($G$11:$G$15,$B2:$F2)),COLUMN($B2:$F2)-1),COLUMNS($A:A))),"") Ctrl Shift Enter
Drag the formula to the right and down as far as needed.

See attachment.

I'll work on returning the search word, not the contents of the cell that the search word is in.

I was not correct in my first replay. In my actual excel file it is returning the multiple finds no problem, just not in the test. if it finds multiple it shows name1; name2 with your first code. So it is just the return just the search term.

Originally Posted by FlameRetired

Super!!

Please update your profile.

Array enter this

Formula:

Please Login or Register  to view this content.

There is no longer a need to save as an *.xlsm (macro) file.

Wow this did an amazing job! Where do I send the care package too? Thank you so much!