Searching For Text

[LWMorton]

I am looking to create a formula that searches a cell full of text and if it contains words from a certain list, the cell will display "0", if it contains words from a different list, then it will display a "1" all the way up to 4. All of the equations I have tried are IF functions that return only a TRUE/FALSE

The text I want to search is the descriptor attached to the students on the right. On the left is a table of the words that are command terms for each level. I hope this makes sense.

[#2 · Accepted solution]

@LWMorton , perhaps

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1))))

in C2 and copy it to another cells

[#3]

@Sergei Baklan that has worked perfectly, you are my saviour!

[#4]

@LWMorton , glad to help

[#5]

@Sergei Baklan for some reason only half the equation works, the terms for level 0 and 1 are coming up as 2s. Any ideas to fix?

[#6]

@LWMorton ,

Sorry, I forgot about 0 column. Plus we have to exclude blank cells, finally as

=IF(SUMPRODUCT((LEN($AF$2:$AF$8)>0)*ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT((LEN($AE$2:$AE$8)>0)*ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT((LEN($AD$2:$AD$8)>0)*ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT((LEN($AC$2:$AC$8)>0)*ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1,
      IF(SUMPRODUCT((LEN($AB$2:$AB$8)>0)*ISNUMBER(SEARCH($AB$2:$AB$8,B2))),
      0
)))))