Compare cells in Excel
In Microsoft Excel, you compare the values of cells with a simple formula. For example, you can determine whether entered data is true or turns out to be incorrect after the comparison.
In the example, the solution words of different players were recorded in rows 2 to 5 of the Excel spreadsheet. Line 10 shows the correct solution. The formula should now be used to determine which player has found the right solution.
How to compare a cell (player's solution word) with another cell (correct solution)
To compare the player's solution word with the correct solution, use the Excel function IDENTICAL with the syntax “IDENTICAL (Text1; Text2)”. This function compares the value specified as “Text1” with “Text2”. For example, use the following procedure to check whether Katja's solution matches the correct solution:
- Enter the function IDENTICAL in line C2 with a formula: = IDENTICAL (
- In the first argument of the formula, write down the first, Katjas, solution. This is in cell B2: = IDENTICAL (B2
- The second argument is the cell you want to compare Katja's solution with. In the example, this is the correct solution in cell B10: = IDENTICAL (B2; B10)
- Confirm the formula with >> Enter <<. The result in cell C2 is “TRUE” because Katja's solution is correct.
- To check the results of all players, insert this formula in column C accordingly for the other players.
In column C you can see that both Katja and Jessica have found the right solution.
To compare a cell (the correct solution) with an entire list (the solutions of all players):
In reverse, use the OR and IDENTICAL functions to check whether one of the players in the Excel list (cells B2 to B5) has found the right solution (B10). In the example, the result is to be output in cell C10. To do this, use the following matrix formula:
- First enter the function OR in line C10: = OR () This function returns "TRUE" if the argument in brackets is true, otherwise "FALSE" is returned.
- Enter the function IDENTICAL as argument of the OR function: = OR (IDENTICAL ())
- In the first argument of the IDENTICAL function, enter the comparison value, in the example the correct solution. This is in cell B10: = OR (IDENTICAL (B10;))
- As a second argument, enter the cell range with which you want to compare the cell (correct solution). In the example, this is the cell range B2 to B5 with the solutions of the players: = OR (IDENTICAL (B10; B2: B5))
- To finish the formula it is not enough to press >> ENTER <<. You must enter the formula as a matrix formula. You can do this by ending the formula with the following key combination: CTRL + SHIFT + ENTER
6. As shown in the picture, “TRUE” is now displayed in field C10, since cell range B2 to B5 has at least one match to cell B10. In the example, this means that at least one player has found the right solution.
This example was created with Microsoft Excel 2003 and Microsoft Excel 2010 Starter. The syntax of the formula may vary slightly in earlier or future versions of Excel.