How to Compare Two Cells in Excel: 3 Simple Methods

Comparing data is a fundamental task in Excel, and often you need to check if the content of one cell is present within another. Whether you’re verifying data entry, searching for specific keywords, or ensuring consistency across your spreadsheets, Excel offers several formulas to efficiently compare cell values. This article will guide you through three straightforward methods to compare two cells in Excel, enhancing your data analysis capabilities.

Method 1: Utilizing the IF and FIND Functions

This method combines the power of the IF and FIND functions to determine if a specific text string (in one cell) exists within a larger text string (in another cell).

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

Explanation:

• FIND(B2,A2): The FIND function attempts to locate the starting position of the text string in cell B2 within the text string in cell A2. If the text in B2 is found within A2, FIND returns the numerical position of the first character of the found text. If not found, it 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 (the starting position), and ISNUMBER returns TRUE. If FIND returns an error (meaning the text was not found), ISNUMBER returns FALSE.
• IF(ISNUMBER(FIND(B2,A2)),TRUE,FALSE): The IF function then uses the TRUE or FALSE 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: Leveraging the MATCH Function (Excel 2016 and later)

For users with Excel 2016 and later versions, the MATCH function provides another effective way to compare cell content, offering a more concise approach.

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

Explanation:

• MATCH(B2,A2,0): The MATCH function searches for the value in cell B2 within cell A2. The 0 as the third argument specifies an exact match. When used in this context with text strings, MATCH checks if B2 is contained within A2. If found, MATCH returns the relative position of the matched value within the searched range (in this case, effectively within the text of A2). If not found, it returns a #N/A error. It’s important to note that in this specific application, we are not using MATCH in its typical array form but rather to check for substring existence.
• IF(MATCH(B2,A2,0)>0,TRUE,FALSE): Similar to the previous method, the IF function evaluates the result of MATCH. If MATCH finds the text, it returns a position greater than 0, making the condition MATCH(B2,A2,0)>0 true, and the IF function returns TRUE. If MATCH does not find the text, it results in an error, which, when coerced to a number in the comparison >0, is treated as an error, but practically speaking for the logic flow, if an error isn’t a number > 0, the IF condition effectively becomes false, and the IF function returns FALSE. (Note: Error handling in direct comparison might vary slightly across Excel versions, but the logical outcome for this comparison remains consistent for practical purposes in this context).

Example:

Using the same example, if cell A2 contains “The quick brown fox jumps over the lazy Cat” and cell B2 contains “Cat”, the formula will return TRUE. If B2 contains “Zebra”, it will return FALSE.

Method 3: Employing SUMPRODUCT with Wildcards

The SUMPRODUCT function, combined with wildcard characters, offers a more flexible approach, especially when dealing with comparing against a range of cells.

=SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2))))>0

Explanation:

• SEARCH(B2,A2): The SEARCH function is similar to FIND but is case-insensitive and allows wildcard characters. Here, it searches for the text in B2 within A2. If found, it returns the starting position; otherwise, it returns a #VALUE! error.
• ISNUMBER(SEARCH(B2,A2)): This checks if SEARCH found the text (returned a number) or not (returned an error), resulting in TRUE or FALSE, respectively.
• --(ISNUMBER(SEARCH(B2,A2))): The double negative (--) converts the TRUE and FALSE values into numerical equivalents, 1 and 0, respectively. This is necessary for SUMPRODUCT to perform its summation.
• SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2)))): In this case, SUMPRODUCT sums up the array of 1s and 0s. Since we are only evaluating a single comparison (between A2 and B2), the sum will be 1 if the text is found and 0 if not.
• SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2))))>0: Finally, we check if the sum is greater than 0. If it is (meaning the text was found at least once), the formula returns TRUE; otherwise, it returns FALSE.

Example:

If cell A2 contains “The quick brown fox jumps over the lazy dog Apple” and cell B2 contains “apple” (case-insensitive search), the formula will return TRUE. If B2 contains “Zebra”, it will return FALSE.

Choosing the Right Method

• For simple, case-sensitive text comparison within single cells, the IF and FIND method is efficient and widely compatible across Excel versions.
• The MATCH function offers a more streamlined approach for Excel 2016 and newer versions, providing similar functionality in a more compact formula.
• The SUMPRODUCT with SEARCH method provides case-insensitive comparisons and more flexibility if you need to extend the comparison to ranges or incorporate more complex criteria.

Each of these methods provides a robust way to compare two cells in Excel, allowing you to effectively analyze and manipulate your data based on text content. Select the method that best aligns with your specific needs and Excel version for optimal efficiency.