How to Compare Text in Excel Using IF

Comparing text in Excel to see if one string contains another is a common task. The IF function, combined with other functions, provides a powerful way to achieve this. Here are three effective methods using IF to compare text:

1. IF with FIND

The FIND function locates the starting position of one text string within another. When paired with IF, you can determine if a specific text string exists within a larger one.

=IF(ISNUMBER(FIND(B2,A2)),TRUE,FALSE)

How it works:

• FIND(B2,A2): Searches for the text in cell B2 within cell A2. If found, it returns the starting position; otherwise, it returns an error.
• ISNUMBER(...): Checks if the result of FIND is a number (meaning the text was found). Returns TRUE if a number, FALSE if an error.
• IF(...): If ISNUMBER is TRUE (text found), the formula returns TRUE. If ISNUMBER is FALSE (text not found), it returns FALSE.

2. IF with MATCH (Excel 2016 and later)

The MATCH function searches for a specific item in a range of cells and returns its relative position. Combined with IF, it can efficiently check for the presence of a text string.

=IF(MATCH(B2,A2,0)>0,TRUE,FALSE)

How it works:

• MATCH(B2,A2,0): Searches for an exact match of the text in B2 within cell A2. The 0 in MATCH ensures an exact match. If found, it returns the position; otherwise, it returns an error. Note that this version of MATCH only works on a single cell, not a range.
• >0: Checks if the result of MATCH is greater than 0 (meaning a match was found).
• IF(...): Returns TRUE if a match is found, FALSE otherwise.

3. IF with SUMPRODUCT and Wildcards

This method leverages SUMPRODUCT and wildcards (*) for flexible partial matching within a range of cells.

=SUMPRODUCT(--(A2:A5*B2))>0

How it works:

• A2:A5*B2: Attempts to multiply each cell in the range A2:A5 by the text in B2. This will result in an error unless wildcards are used in B2 to define the search. For example, to find any cell containing “apple”, B2 should contain "*apple*".
• --(...): Converts the resulting array of errors and numbers (where a number represents a match) into an array of 0s and 1s.
• SUMPRODUCT(...): Sums the values in the array. A sum greater than 0 indicates at least one match.
• >0: Checks if the sum is greater than 0.
• IF(...): Returns TRUE if the sum is greater than 0 (at least one match found), FALSE otherwise. Note that without wildcards in B2, this will always return FALSE.

Conclusion

These three methods demonstrate how to compare text in Excel using the IF function. Choose the method best suited to your specific needs: FIND for simple containment checks within a single cell, MATCH for exact matches within a single cell in newer Excel versions, and SUMPRODUCT with wildcards for partial matches across a range of cells. Remember to adjust cell references as needed for your data.