Forum Discussion

katieo8081's avatar
katieo8081
Copper Contributor
Aug 12, 2021

Multiple IF Statement

 I'm trying to use multiple IF(SEARCH) functions to find text within a specific column to return other specific text.  I have attached a sample.  I would like to write an IF statement that searches f...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 15, 2021

katieo8081 

IF( SEARCH... practically never works.  IF() evaluates condition, depends on it returns TRUE or FALSE calculate the result. But if we it returns an error, entire IF() returns error.

 

Usual combination for such cases is to use IF( ISNUMBER( SEARCH( ... 

ISNUMBER returns TRUE if text is found and its position is returned. If error it has another (not number) type, thus ISNUBER returns FALSE.

 

Another point it's not clear would you like return color as very first word of the text or it could be at any place of the text. If former it's easier, just find position of first space and LEFT() text on it.

If the latest I'd create in any place of the workbook the list of words to be found, like

with that formula could be

=INDEX($D$2:$G$2,
  MMULT(--ISNUMBER(SEARCH($D$2:$G$2,[@[Item Description]])),
        TRANSPOSE($D$1:$G$1)))

Resources