How to Compare 2 Cells in Excel: Different Methods for Text Matching

Comparing cells in Excel is a fundamental task for data analysis and manipulation. Specifically, you might need to check if the text from one cell is contained within another. Whether you are verifying data entries, searching for specific keywords within larger text strings, or cleaning up your datasets, Excel provides several powerful formulas to compare cell content. This article will explore different methods to effectively compare two cells in Excel to determine if one cell’s text is present in another.

Method 1: Utilizing the IF and FIND Functions

The combination of the IF and FIND functions is a robust way to check if a cell contains the text of another. This method is case-sensitive.

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

Explanation:

• FIND(B2,A2): This part of the formula attempts to locate the starting position of the text in cell B2 within the text in cell A2. If the text from B2 is found in A2, FIND returns the numerical position of the first character of the found text. If the text is not found, FIND returns a #VALUE! error.
• ISNUMBER(FIND(B2,A2)): The ISNUMBER function checks if the result of the FIND function is a number. If FIND successfully locates the text, it returns a number, and ISNUMBER will return TRUE. If FIND returns an error (meaning the text was not found), ISNUMBER will return FALSE.
• IF(ISNUMBER(FIND(B2,A2)),TRUE,FALSE): The IF function then uses the boolean result from ISNUMBER. If ISNUMBER is TRUE (text found), the IF function returns TRUE. If ISNUMBER is FALSE (text not found), the IF function returns FALSE.

Example:

If cell A2 contains “The quick brown fox jumps over the lazy dog Apple” and cell B2 contains “Apple”, the formula will return TRUE. If B2 contains “Zebra”, the formula will return FALSE.

Method 2: Employing the MATCH Function (Excel 2016 and Later)

For users with Excel 2016 and later versions, the MATCH function offers another approach. Similar to FIND, MATCH is also case-sensitive in this context.

=IF(ISNUMBER(MATCH("*"&B2&"*",A2,0)),TRUE,FALSE)

Explanation:

• "*" & B2 & "*": Here, wildcards (*) are used in conjunction with cell B2. The asterisk wildcard represents any sequence of characters. By placing wildcards before and after B2, we are essentially searching for cell B2‘s text anywhere within cell A2.
• MATCH("*"&B2&"*",A2,0): The MATCH function searches for the wildcard-enclosed text from B2 within cell A2. The 0 as the third argument specifies an exact match, but with wildcards, it effectively looks for a “contains” match. If a match is found, MATCH returns the relative position of the matched item within the range (in this case, position within the text string, though this position number is not directly relevant to our TRUE/FALSE result). If no match is found, MATCH returns a #N/A error.
• ISNUMBER(MATCH("*"&B2&"*",A2,0)): Similar to Method 1, ISNUMBER checks if MATCH returned a number (indicating a successful match) or an error (#N/A, indicating no match).
• IF(ISNUMBER(MATCH("*"&B2&"*",A2,0)),TRUE,FALSE): The IF function then returns TRUE if ISNUMBER is TRUE (match found) and FALSE if ISNUMBER is FALSE (match not found).

Example:

Using the same example as before, if A2 is “The quick brown fox jumps over the lazy dog Apple” and B2 is “Apple”, this formula also returns TRUE. For B2 as “Zebra”, it returns FALSE.

Method 3: Using SUMPRODUCT with Wildcards for Range Comparisons

The SUMPRODUCT function combined with wildcards provides a more versatile approach, particularly when you want to compare one cell against a range of cells. While the original example in the prompt was slightly different, let’s adapt SUMPRODUCT to directly compare two single cells for text containment, similar to methods 1 and 2, for clarity and consistency in our comparison of two cells.

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2)))),TRUE,FALSE)

Explanation:

• SEARCH(B2,A2): The SEARCH function is similar to FIND but is not case-sensitive. It looks for the starting position of the text in B2 within A2. If found, it returns the starting position; otherwise, it returns a #VALUE! error.
• ISNUMBER(SEARCH(B2,A2)): Checks if SEARCH returned a number (text found) or an error (text not found), resulting in TRUE or FALSE, respectively.
• --(ISNUMBER(SEARCH(B2,A2))): The double negative (--) converts the TRUE or FALSE boolean values into numerical values (1 for TRUE, 0 for FALSE). This is necessary for SUMPRODUCT to work correctly in this context.
• SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2)))): In this case, SUMPRODUCT is used to sum the array resulting from the double negative operation. Since we are only evaluating one comparison (between A2 and B2), the sum will be either 1 (if text is found) or 0 (if text is not found).
• IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2)))),TRUE,FALSE): The IF function checks if the SUMPRODUCT result is greater than 0. If it is (meaning the text was found at least once), it returns TRUE; otherwise, it returns FALSE.

Key Advantage of SEARCH in Method 3: Unlike FIND and MATCH in the previous methods, SEARCH is case-insensitive. This means it will find matches regardless of whether the text in cell B2 is in the same case as in cell A2.

Example:

If A2 is “The quick brown fox jumps over the lazy dog APPLE” and B2 is “apple”, this formula will return TRUE because SEARCH is not case-sensitive. For B2 as “Zebra”, it will return FALSE.

Choosing the Right Method

• For case-sensitive comparisons, either the IF and FIND combination (Method 1) or the MATCH function (Method 2) are excellent choices. FIND is slightly simpler to understand for beginners.
• For case-insensitive comparisons, Method 3 using SUMPRODUCT and SEARCH is the most suitable.
• If you need to compare one cell’s content against a range of cells, Method 3 using SUMPRODUCT can be easily adapted to compare against a range by modifying the SEARCH function’s arguments to include a range instead of a single cell for the first argument (the cell to search within).

These methods provide you with flexible options to compare two cells in Excel based on whether you need a case-sensitive or case-insensitive search. Choose the method that best fits your specific needs and data analysis objectives in Excel.