May 20 2019 03:50 AM
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.
May 20 2019 06:04 AM
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
May 20 2019 02:45 PM
@Sergei Baklan that has worked perfectly, you are my saviour!
May 21 2019 03:26 AM
@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?
May 21 2019 11:08 AM
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 )))))
May 20 2019 06:04 AM
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