Search cell B for a range of specific text and place corresponding text in cell A

Hi all, I have a formula that is more or less working but is a bit clunky and I assume there is a more efficient method.

I have data in column B. I want to search each cell in column B for a range of specific text (listed in column C) and if found, place the text from the corresponding row in column D into column A.

I have set this up with the followings IFS function eg for cell A1:

=IFS(ISNUMBER(SEARCH($C$1,B1)),$D$1,ISNUMBER(SEARCH($C$2,B1)),$D$2,ISNUMBER(SEARCH($C$3,B1)),$D$3... etc etc

So it's searches B1 for the text listed in C1. If found, it places the text in listed D1 into A1. It then searches for the text listed in C2. If found, it places the text in listed D2 into A1, and so.

To search for all the text listed in column C, I have to duplicate the formula many times. Is there a way to do this more efficiently, where it searches through the entire column C rather than having to search each cell individually? Something like

=IFS(ISNUMBER(SEARCH(C1:C9,B1)),D1:D9)

I've tried this but get a #SPILL! error. Any ideas?

Hi see yellow banner on how to add an example sheet with some data and manually added expected results
Thanks

Not sure you 'd like to search for full or partial match, but I offer 2 cases of match in attachment.

This, maybe:

=INDEX(D:D,AGGREGATE(15,6,ROW($C$1:$C$10)/(ISNUMBER(SEARCH($C$1:$C$10,B1))),1))

copied down.

You're welcome.

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($C$1:$C$10)/(ISNUMBER(SEARCH($C$1:$C$10,B1))),1)),"")

should fix that.

You're welcome.

________________________________________

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.