How to Compare 2 Values in Excel

Comparing two values in Excel is a common task for data analysis, cleaning, and manipulation. Whether you need to find exact matches, partial matches, or simply determine if one value is greater than another, Excel provides several functions and techniques to accomplish this. This guide will explore three effective methods for comparing two values in Excel, using formulas that cater to different needs and Excel versions.

Three Ways to Compare Text Values in Excel

Here are three distinct formulas you can use to compare text values in Excel:

1. Comparing Text Using IF and FIND

The IF and FIND functions combined provide a powerful way to check if one text string contains another. The FIND function searches for the position of a specific substring within a larger string. If the substring is found, FIND returns its starting position. If not found, it returns an error value. The ISNUMBER function then checks if the result of FIND is a number (meaning the substring was found). Finally, the IF function returns TRUE if the substring is found and FALSE otherwise.

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

In this formula:

• A2 contains the larger text string you want to search within.
• B2 contains the substring you want to find.

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

2. Comparing Text with the MATCH Function (Excel 2016 and later)

For Excel 2016 and later versions, the MATCH function offers a more concise way to compare text values. MATCH searches for a specific value within a range and returns its relative position. By setting the match_type argument to 0, we enforce an exact match.

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

In this formula:

• B2 is the value you want to find.
• A2 is the cell you want to search within.
• 0 indicates an exact match.

Example: If A2 contains “The quick brown fox jumps over the lazy Cat” and B2 contains “Cat”, the formula will return TRUE.

3. Comparing Text Using SUMPRODUCT and Wildcards

The SUMPRODUCT function, combined with wildcards, offers a versatile approach to partial text matching. This method allows you to check if a cell contains a specific substring, even if it’s not an exact match.

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

In this formula:

• A2:A5 is the range of cells you want to search.
• B2 contains the substring you’re looking for.
• The * wildcard represents any sequence of characters.

Example: If A2 contains “Apple Pie”, A3 contains “Green Apple”, and B2 contains “Apple”, the formula will return TRUE for both A2 and A3. It would return FALSE if searching for “Zebra” in those cells.

Conclusion

Excel provides multiple ways to compare two values, offering flexibility for various data analysis scenarios. By understanding the functionalities of functions like IF, FIND, MATCH, and SUMPRODUCT, you can efficiently compare text values to identify matches, partial matches, or differences within your data. Choosing the right formula depends on your specific needs and the version of Excel you are using. Experiment with these methods to find the best solution for your data comparison tasks.