Is there a way to use isnumber and search to pull from multiple cells and search multiple cells to return to multiple cells?

Question

I have cobbled together the following formula, which works :

=IF(ISNUMBER(SEARCH(F7, $B$11:$B$53)), $C$11:$C$53,  
      IF(ISNUMBER(SEARCH(F8, $B$11:$B$53)), $C$11:$C$53, 
           IF(ISNUMBER(SEARCH(F9, $B$11:$B$53)), $C$11:$C$53, 
                IF(ISNUMBER(SEARCH(F10, $B$11:$B$53)), $C$11:$C$53, ""))))

(each "new line" in my post is a space)

F7, F8, F9, and F10 are search terms that I want to find in column B11:B53. Search terms in my "F" array are partial matches to text strings in B11:B53. I am then pulling corresponding data from C11:C53, and putting it in the corresponding rows in column F11:F53.

Is there a simpler way to write this formula that will allow scaling?

I would like to be able to easily add a search term in F1:F6 and easily expand my search array, without having to add

IF(ISNUMBER(SEARCH(F6, $B$11:$B$53)), $C$11:$C$53,

to my formula FOR EACH SEARCH TERM ADDED. If I want to add 10 new criteria the formula will become unwieldy.

I am also scaling this formula across multiple columns, so I can search column "B" and pull data from column "C", and place it in the same column as my search array, with different lists of search terms in each column.

Currently if one search term cell (ie F8) is blank, my entire column gets filled with data from C, rather than just the data that should be.

My goal is to have my entire column "C" be broken down across multiple columns based on what is in column B, and what is in the "Search list" (in this example, F7:F10)

Any help would be appreciated

Answer 1

This is an improvement. Try a formula like this in cell C11 if you have 4 search criteria entered into F7:F10:

=IF(SUM(COUNTIF(B11,"*"&F$7:F$10&"*"))=(ROWS(F$7:F$10)),B11,"")

It counts your criteria rows (F7:F10 in this case, so 4 criteria) and returns the value in B11 if the sum of findings matches the number of criteria rows. COUNTIF() can accept a range and permits wildcards.

To ignore a criterion, just put a ? or * into it.