Microsoft Excel: if match formula to check whether two or multiple cells are equal

Now in this tutorial, we will be discussing how one can effectively construct the If match formula in Microsoft Excel accurately so that it would be returning out the logical values, text (custom), as well as value from another cell respectively.

Moreover, the Microsoft Excel formula which can be used for the purpose of seeing that if two cells match could be as simple as like: A1=B1. However, there may be various circumstances when this obvious solution would not work or to produce the results different from what we have expected respectively.

Now we will be moving further while discussing the various ways in order to compare cells in Microsoft Excel to find an optimal solution for our task effectively.

1. Checking if two respective cells match or not
   1. If two individual cells are equal, then it will return TRUE.
   2. If two individual cells are the same, then the value will return.
   3. If in case two cells get matched, then it will be returning the other cell as well.
   4. Case-sensitive if it matches the formula in Microsoft Excel.
2. How one can check if multiple cells are equal?
3. If cells match any cell in a range in Microsoft Excel.
4. How one can check if the two ranges are equal or not in the given Excel sheet?

How can one check if two cells match in Microsoft Excel?

It was well known that many variations of the Microsoft Excel If match formula exist as well. Now after that we will be efficiently reviewing all the examples given below and then according to that we will be choosing the one that will works best for our scenario respectively.

a) If two cells are equal, then it will return TRUE.

The easiest one is "If one cell equals another then true" and the Microsoft Excel formula is as follows:

• For example: In order to make comparison of cells in respective to the columns A and B in each row, we will be entering this formula in the column C2, and then copy it down the column.

As an outcome, we will be getting TRUE if two cells are the same and FALSE otherwise:

Important Notes:

• The above used formula will be returning two Boolean values: if two cells are equal - TRUE, and if not identical then FALSE. To only return the TRUE values, we make use of an IF statement as shown in the next example.
• This formula is case-insensitive so that it will be treating the uppercase as well as the lowercase letters as the same characters. If the text case matches, then make use of this case-sensitive formula.

b) If two cells match, then it will return a value.

For the purpose of returning our value if two cells matches, constructs an IF statement by making use of this pattern as well:

• For example: To compare A2 and B2 and then return "yes" if they contain the same values, "no" otherwise, the formula is:

And if in case we only want to return a value when the cells are equal, then will be supplying an empty string ("") for value_if_false as well.

If it matches, then it would be returning yes:

If it matches, then TRUE:

If one cell equals another, then it will be returning another cell in the respective Excel sheet

Here it is a variation of the Microsoft Excel if-match formula that will solve this specific task effectively that is comparing of the values in two cells. And if in case the data get matches, and then will copy a value from other cell effectively.

Moreover, in the respective Microsoft Excel language, it could be effectively formulated as like this:

If in case we want to check the items in columns A as well as in the column B and then after that we will be returning a value from column C if the text matches exactly, the formula in D2, copied down as follows:

The case-sensitive formula to see if two cells matches or not in the Excel sheet

If in a scenario, when we are dealing with case-sensitive values which are in the form of the text, then in that respective scenario we can easily make use of the EXACT function for the purpose of comparing the cells exactly:

• For example: If in case we want to compare the items in cell A2 as well as in the cell B2 and returning "yes" if the [articular used text gets matches exactly or "no" if any difference is found, so for this to be get achieved; we can make use a formula as well:

How can one check if multiple cells are equal in an Excel sheet

In comparison with the two cells, checking out the multiple cells for the matches can also be done in different ways which are as follows:

AND formula to see if multiple cells match

Now in order to check if the multiple values match or not, then in that case, we can make use of the AND function with two or more logical tests as well:

• For example: In these, we will see if the cells A2, B2, and C2 are equal, and the formula is:

Besides all these, in the dynamic array Excel we can also use the syntax below. As in Microsoft Excel version 2019 as well as in the lower version of the Excel, this could be only working as a traditional CSE array formula respectively, and completed by pressing the Ctrl + Shift + Enter keys.

Furthermore, the outcome of both the AND formulas are the logical values that are either TRUE or FALSE.

That can be efficiently used for the purpose of returning our values and also wrapping AND in the IF function as like this:

COUNTIF formula in order to check if multiple columns match in Excel sheet

Another most important method that can be used to check for numerous matches is by just making use of the COUNTIF function in this form respectively:

In this, the range is a range of cells that must need to be compared against each other, and the cell is any single cell in the content, whereas n is the number of the cells in the range.

And for our sample dataset, the formula can be written in the below mentioned format as well:

And if we are comparing a lot of columns, then in that scenario the COLUMNS function can get the cells' count (n) for us automatically without any human intervention as well:

And the IF function will help us to return anything that we actually want as an outcome:

The case-sensitive formula for multiple matches in Excel sheet

As checking the two cells together, we can employ the EXACT function for the purpose of performing out the exact comparison, including the letter case. And in order to handle multiple cells, EXACT function need to be nested into the AND function like this:

Besides all these, in the Microsoft Excel 365 as well as in Microsoft Excel 2021, due to support for dynamic arrays, it will be working as a normal formula.

• For example: If in case we need to check that if the cells that is from cell A2:C2 containing the same values, then in that case we will be making use of a case-sensitive formula which are as follows:

Moreover, in combination with IF, it will be taking this shape as follows:

Checking if the cell matches to any particular cell in a range in Microsoft Excel or not

Now in order to see if a cell matches to any other particular cell in a given range or not, we will be then utilizing one of the following formulas as well:

OR function

The OR function is primarily best suited to be used for the purpose of checking 2 - 3 cell respectively.

Besides all these, the Microsoft Excel 365 as well as the Excel 2021 will effectively understand this syntax as well:

And in Microsoft Excel 2019 and in the lower version of the Excel, this could be entered as an array formula by just pressing the Ctrl + Shift + Enter shortcut from our keyboard respectively.

Use of COUNTIF function in Microsoft Excel

And now for the purpose of returning Yes or No or any other values which we want, and we specifically know what to do - we will be nesting one of the above formulas in the logical test of the IF function respectively:

• For example:

Checking if the two ranges are equal or not in the given Excel sheet

Now for the purpose of comparing the two ranges cell-by-cell and then returning the logical value TRUE if all the given cells in the corresponding positions match, after that we will be supplying the equally sized ranges to all the logical test of the AND function in an efficient manner as well:

• For example: If in case we want to make comparison of the Matrix A in B3:F6 and Matrix B in B11:F14, then the formula to be achieve the above could be as follows:

And if we want to get Yes or No as an outcome, then in that case we can make use of the following IF AND combination respectively: