How to Check if Two Values Are Equal in Excel

Publish date: 2024-09-20

Yes, you can tell if two numbers are equal in an instant just by looking at them. But that won't be the case when you're looking at larger numbers or if you want to test multiple numbers in two columns to see if they're equal.

Like everything else, Excel has a remedy that makes this test easier. There are many formulas you could write that check if two values in Excel are equal or not. The simplest methods are using an expression, the DELTA function, and the IF function.

1. Test if Two Values Are Equal With an Expression

The easiest way to determine whether two cells are equal is by using a simple Boolean expression. This way, you can get the job done with a simple logical operator without using any Excel functions.

  • Select the cell where you want to display the results.
  • In the formula bar, enter the formula below:
    =A1=B1
  • Press Enter.

    • If the expression is true and A1 equals B1, Excel will return TRUE. Otherwise, it will return FALSE. That's it! This method is extraordinarily simple since it doesn't use any functions. However, for the same reason, it's not suitable for complex calculations as you can't combine it with other functions.

    2. Test if Two Values Are Equal With the DELTA Function

    DELTA is an Excel function that tests whether two numerical values are equal. Similar to using an expression, the DELTA function has two preset outputs. If the two values are equal, DELTA will return 1; if they are not, it will return 0.

    =DELTA(number1, number2)

    The DELTA function can only operate on numbers and cannot test whether two text strings are equal. If number2 is left blank, DELTA will assume that it is zero.

    Count Equal Values With the DELTA Function

    DELTA can be easily coupled with other Excel functions to count the equal number pairs in a list. In this sample spreadsheet, we have two columns of numbers, and we want to see how many pairs are equal.

    For this example, we will use the DELTA function to check if the numbers in each pair are equal. Then, we will get the count of equal pairs using Excel's COUNTIF function. Here's how:

  • Select the first cell in the column where you want to check if the numbers are equal. In this example, we'll use cell C2.
  • In the formula bar, enter the formula below:
    =DELTA(A2, B2)
  • Press Enter.

    • DELTA will now tell you if the two numbers are equal or not. The formula calls DELTA to test the numbers in A2 and B2 and see if they're equal. The formula then returns 1 to indicate that they were equal. If the two numbers weren't equal, the formula would return 0.

    Grab the fill handle and drop it on the cells below to get the test results for all the numbers. Now, you can see which pairs have equal numbers using the DELTA function. Unfortunately, that's as far as the DELTA function goes. You'll have to use Excel's COUNT function to count the number of pairs with equal numbers. One candidate for this task is the COUNTIF function.

    COUNTIF can go through a range of cells and return the number of cells that meet a specific condition. Since DELTA returned 1 to indicate equal pairs, you can ask COUNTIF to go through DELTA's output and count the cells containing 1. This will return the number of equal pairs.

  • Select the cell where you want to show the count of pairs with equal numbers.
  • In the formula bar, enter the formula below:
    =COUNTIF(C2:C10, "=1")
  • Press Enter.

    • The formula calls on COUNTIF to check the results from the DELTA function (C2 to C10) and return the number of cells that equal 1. Remember that the DELTA function returns 1 when the two values are equal; therefore, this formula will count the number of pairs with equal numbers. In the formula, you can replace 1 with 0 to get the count of pairs with unequal numbers.

    The DELTA function is a simple enough method to check if two numbers are equal. However, if you don't like the binary output of the DELTA function, you can use the IF function instead to get a custom output.

    3. How to Test if Two Values Are Equal With the IF Function

    IF is one of Excel's logical functions. It takes a condition and then returns a user-specified output based on whether the condition is met.

    =IF(logical_Test, Output_If_True, Output_If_False)

    The IF function conducts a logical test, and if the test result is true, it returns Output_If_True. Otherwise, it returns Output_If_False.

    IF is in a whole other league than DELTA. Where DELTA could only determine whether two numbers are equal, IF allows you to use Excel's logical operators to run any kind of test. However, in this context, the IF function works in the same way as the DELTA function, except that you can have it output a specific string.

    To check if two values are equal with the IF function, you need to run a logical test where you put the two cells as equal. Then, you'll need to specify outputs for the two scenarios: The test result being true, and the test result being false.

    =IF(A1=B1, "Yes", "No")

    For example, the above formula checks whether A1 equals B1, then returns Yes or No depending on the results.

    Another advantage of IF over DELTA is that it isn't restricted to numbers and works with text strings, too. You can even use multiple IF functions together to define multiple conditions.

    Count Equal Values With the IF Function

    Let's put the IF function to use in the same example. The goal is to see which number pairs are equal and then count the equal ones using the COUNTIF function.

  • Select the first cell in the column where you want to return the test results. This will be cell C2 for this example.
  • In the formula bar, enter the formula below:
    =IF(A2=B2, "Yes", "No")
  • Press Enter.

    • The IF function will now tell you whether the two values are equal or not. The formula here tests cells A2 and B2 to see if they're equal. If the two cells are equal, the formula will return Yes. Otherwise, the formula will return No.

    Grab the fill handle and drop it on the cells below. The IF function will now test each pair and return the results accordingly.

    You can count the equal pairs using the COUNTIF function in the same way as the DELTA function. The only difference is that instead of counting the cells that equal 1, you should count the cells containing the string Yes. This is because the IF formula you wrote returns Yes for pairs with equal values.

    Here's how you can count the equal pairs:

  • Select the cell where you want to return the count. For this example, we've chosen cell E2.
  • In the formula bar, enter the formula below:
    =COUNTIF(C2:C10, "=Yes")
  • Press Enter.

    • COUNTIF will now tell you how many pairs contain equal values. The formula summons COUNTIF to look through the IF results (C2 to C10) for cells containing the string Yes and then returns the count of cells that do. Since IF returned Yes for equal pairs, this will be the count of equal pairs.

    Test if Two Values Are Equal With Excel Functions

    There are plenty of formulas you could write up to check whether two values are equal in Excel. The most straightforward approach is to write the expression as a Boolean. If you want to use functions, the DELTA function exclusively serves this purpose, whereas the IF function can accomplish many feats, including this one.

    Now that you know how to use Excel to check if two cells are equal, you can forget about going through the cells yourself. Excel exists to take the burden of counting and calculating off your shoulders. This is one minor instance of how Excel can do that and make your life easier.

    ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGifqK9dqbxur8eemqRlpKy8bsLApayeq12avraty2agp2WVrbCmuI4%3D