Searching For Text

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.

DRAFT Comparative Paragraph Rubric.xlsx20 KB

excel

Formulas and Functions

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

DRAFT Comparative Paragraph Rubric.xlsx14 KB

5 Replies

SergeiBaklan
Diamond Contributor
May 20, 2019
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
DRAFT Comparative Paragraph Rubric.xlsx14 KB
- LWMorton
  Copper Contributor
  May 20, 2019
  SergeiBaklan that has worked perfectly, you are my saviour!
  - SergeiBaklan
    Diamond Contributor
    May 20, 2019
    LWMorton , glad to help

Forum Discussion

Searching For Text

5 Replies

Resources