Excel: check if two cells match or multiple cells are equal

The tutorial will teach you how to construct the If match formula in Excel, so it returns logical values, custom text or a value from another cell.

An Excel formula to see if two cells match could be as simple as A1=B1. However, there may be different circumstances when this obvious solution won't work or produce results different from what you expected. In this tutorial, we'll discuss various ways to compare cells in Excel, so you can find an optimal solution for your task.

How to check if two cells match in Excel

There exist many variations of the Excel If match formula. Just review the examples below and choose the one that works best for your scenario.

If two cells equal, return TRUE

The simplest "If one cell equals another then true" Excel formula is this:

cell A = cell B

For example, to compare cells in columns A and B in each row, you enter this formula in C2, and then copy it down the column:

=A2=B2

As the result, you'll get TRUE if two cells are the same, FALSE otherwise: If two cells equal, return TRUE

Notes:

  • This formula returns two Boolean values: if two cells are equal - TRUE; if not equal - FALSE. To only return the TRUE values, use in IF statement as shown in the next example.
  • This formula is case-insensitive, so it treats uppercase and lowercase letters as the same characters. If the text case matters, then use this case-sensitive formula.

If two cells match, return value

To return your own value if two cells match, construct an IF statement using this pattern:

IF(cell A = cell B, value_if_true, value_if_false)

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

=IF(A2=B2, "yes", "no") If two cells match, return some value

If you only want to return a value if cells are equal, then supply an empty string ("") for value_if_false.

If match, then yes:

=IF(A2=B2, "yes", "")

If match, then TRUE:

=IF(A2=B2, TRUE, "") If two cells match, return Yes or TRUE

Note. To return the logical value TRUE, don't enclose it in double quotes. Using double quotes will convert the logical value into a regular text string.

If one cell equals another, then return another cell

And here's a variation of the Excel if match formula that solves this specific task: compare the values in two cells and if the data match, then copy a value from another cell.

In the Excel language, it's formulated like this:

IF(cell A = cell B, cell C, "")

For instance, to check the items in columns A and B and return a value from column C if text matches, the formula in D2, copied down, is:

=IF(A2=B2, C2, "") If one cell equals another, then return another cell

Case-sensitive formula to see if two cells match

In situation when you are dealing with case-sensitive text values, use the EXACT function to compare the cells exactly, including the letter case:

IF(EXACT(cell A, cell B), value_if_true, value_if_false)

For example, to compare the items in A2 and B2 and return "yes" if text matches exactly, "no" if any difference is found, you can use this formula:

=IF(EXACT(A2, B2), "Yes", "No") Case-sensitive formula to see if two cells match

How to check if multiple cells are equal

As with comparing two cells, checking multiple cells for matches can also be done in a few different ways.

AND formula to see if multiple cells match

To check if multiple values match, you can use the AND function with two or more logical tests:

AND(cell A = cell B, cell A = cell C, …)

For example, to see if cells A2, B2 and C2 are equal, the formula is:

=AND(A2=B2, A2=C2)

In dynamic array Excel (365 and 2021) you can also use the below syntax. In Excel 2019 and lower, this will only work as a traditional CSE array formula, completed by pressing the Ctrl + Shift + Enter keys together.

=AND(A2=B2:C2)

The result of both AND formulas is the logical values TRUE and FALSE.

To return your own values, wrap AND in the IF function like this:

=IF(AND(A2=B2:C2), "yes", "")

This formula returns "yes" if all three cells are equal, a blank cell otherwise. Checking if multiple cells are equal

COUNTIF formula to check if multiple columns match

Another way to check for multiple matches is using the COUNTIF function in this form:

COUNTIF(range, cell)=n

Where range is a range of cells to be compared against each other, cell is any single cell in the range, and n is the number of cells in the range.

For our sample dataset, the formula can be written in this form:

=COUNTIF(A2:C2, A2)=3

If you are comparing a lot of columns, the COLUMNS function can get the cells' count (n) for you automatically:

=COUNTIF(A2:C2, A2)=COLUMNS(A2:C2)

And the IF function will help you return anything you want as an outcome:

=IF(COUNTIF(A2:C2, A2)=3, "All match", "") Checking if multiple columns match

Case-sensitive formula for multiple matches

As with checking two cells, we employ the EXACT function to perform the exact comparison, including the letter case. To handle multiple cells, EXACT is to be nested into the AND function like this:

AND(EXACT(range, cell))

In Excel 365 and Excel 2021, due to support for dynamic arrays, this works as a normal formula. In Excel 2019 and lower, remember to press Ctrl + Shift + Enter to make it an array formula.

For example, to check if cells A2:C2 contain the same values, a case-sensitive formula is:

=AND(EXACT(A2:C2, A2))

In combination with IF, it takes this shape:

=IF(AND(EXACT(A2:C2, A2)), "Yes", "No") Case-sensitive formula to check multiple cells for matches

Check if cell matches any cell in range

To see if a cell matches any cell in a given range, utilize one of the following formulas:

OR function

It's best to be used for checking 2 - 3 cells.

OR(cell A = cell B, cell A = cell C, cell A = cell D, …)

Excel 365 and Excel 2021 understand this syntax as well:

OR(cell = range)

In Excel 2019 and lower, this should be entered as an array formula by pressing the Ctrl + Shift + Enter shortcut.

COUNTIF function

COUNTIF(range, cell)>0

For instance, to check if A2 equals any cell in B2:D2, any of these formulas will do:

=OR(A2=B2, A2=C2, A2=D2)

=OR(A2=B2:D2)

=COUNTIF(B2:D2, A2)>0

If you are using Excel 2019 or lower, remember to press Ctrl + Shift + Enter to get the second OR formula to deliver the correct results. Checking if a cell matches any cell in range

To return Yes/No or any other values you want, you know what to do - nest one of the above formulas in the logical test of the IF function. For example:

=IF(COUNTIF(B2:D2, A2)>0, "Yes", "No") Check if a cell is equal to any cell in range and return Yes/No as the result.

For more information, please see Check if value exists in a range.

Check if two ranges are equal

To compare two ranges cell-by-cell and return the logical value TRUE if all the cells in the corresponding positions match, supply the equally sized ranges to the logical test of the AND function:

AND(range A = range B)

For example, to compare Matrix A in B3:F6 and Matrix B in B11:F14, the formula is:

=AND(B3:F6= B11:F14)

To get Yes/No as the result, use the following IF AND combination:

=IF(AND(B3:F6=B11:F14), "Yes", "No") Checking if two ranges are equal

That's how to use the If match formula in Excel. I thank you for reading and hope to see you on our blog next week!

Table of contents

188 comments

  1. Want to compare the value of Cell A and B of Sample 1 excel file with Cell A and B of Sample 2 excel file together at a time, if match then the value of cell D (sample 2) will be copied at Sample 1 file.

    Please help

  2. Do you have a formula for this condition : ( if cell1=x and cell2 y → cell3 = z) ? thank you .

  3. Hi There, I would love some help. I have two sheets with the same data range, State and Email. I am attempting to cross reference if the two sheets have matching information. I have used this formula but it is not working.

    =IF(AND(C2='Response Data'!B2,'Agent TMT State Data'!,D2='Response Data'!C2),TRUE

      • Hi Alex,

        I have two worksheets named Response Data and Agent TMT Data. Each worksheet has a column for State and Email, there are roughly 6000 records.
        I want to create a formula that will give me a response of Match/No Match or True/False if the row that contains the data for state and email is not matching.

        EG: Response data has C2 test@test. com and D2 Texas, TMT Agent data has the same information in the worksheet (test @test. com and Texas in the same row)

        I hope this makes sense

        • Hello! If I understand your task correctly, the following formula should work for you:

          =ISNUMBER(MATCH('Agent Response Data'!C2&'Agent Response Data'!D2, 'TMT State Data'!C2:C30&'TMT State Data'!D2:D30,0))

          We have a tool that can solve your task in a couple of clicks - Compare Two Tables. This tool can compare two tables or lists by any number of columns, identifying matches/differences (like formulas) and highlighting them (like conditional formatting). The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

  4. Hi, I have a big excel sheet containing data on M&As which I have to combine.

    I have three columns which have to match, two columns with Ticker Symbols and one with the corresponding ISIN code of a company. If the ticker symbols match I want to get the corresponding ISIN code for the company and its deal (in some cases it is one deal, others could be 20). But for some reason it does not check all cells, rather if I write a function it automatically jumps to the next ticker symbol. Do you perhaps know what function to use?

  5. Can I get help with this formula? I'm getting a syntax error message when I enter this and can't figure out the issue. I just want it to pull a value based on different ranges.

    =IFS(COUNTIF(Reference!B2:B64,B3)>0,Reference!B1, [COUNTIF(Reference!C2:C64,B3)>0],Reference!C1, [COUNTIF(Reference!D2:D64,B3)>0],Reference!D1)

  6. I have two drop down list B2 and D2. If B2 equals to a text then D2 should only have a certain lot of values/text available to use and the rest must be grey out. what formula to i use?

  7. Hi,
    I have two different diagrams. What they have in common is that they both have one column with the same material number, but sorted in different rows.
    But, the first diagram have a column with the change of the quantities of how much materials will increase or decrease.
    And the other diagram have a cost per unit column.

    I must somehow figure out how much will the cost increase or decrease in every row for every material number.

    Maybe I can use the material number. If they match equally,then I can multiply the Change in Quantities with Cost per Unit. But how?

  8. Is there a way to make a formula , if any cell in one column match another cell so number should be taken from specific cell

    • Hi! Based on your description, it is hard to completely understand your task. To get a value by condition, you can use the IF formula.
      If you want to search for a value in a column and, if it is found, get the value from another cell, use the MATCH function. The ISNUMBER function returns TRUE when the value is found. For example:

      =IF(ISNUMBER(MATCH(B2, A2:A11,0)),C2)

  9. I want to get a yes or no answer. If G3 equals H1 or I1 then no, otherwise yes

    Column G could have PP01, PP02, PP01A, PP02A. H1 = PP01, I1 = PP02

  10. First off, great simple instructions! Very helpful.

    Using this formula =A2=B2 (If two cells equal, return TRUE), is there a way to have True in Green color font, and False in Red color font.

  11. I want to display a stock closing price which will change daily in the same cell(A2). I want to compare with cell just to the right of it(B2) to see if the first cell(A2) is higher than the second cell(B2). If it is then copy A2 to B2, if not do nothing. Is this possible?

  12. I have 2 spreadsheets.

    1 has the correct supplier name, inv number and description
    1 has the wrong supplier name, but correct invoice number and description.

    Is there a way for me to move the correct supplier name into the second spreadsheet without having to it manually? the wrong supplier's are scattered across multiple rows so I cannot just copy and paste and it's over 5k of rows so very time consuming to do manually.

    • Hello! Using the VLOOKUP or INDEX MATCH functions, you can use the invoice number to find the supplier's name in the second table and enter it in a separate column in the first table. But you cannot replace the wrong name with the correct name using formulas. Also, Excel has no way of knowing if it found the correct name.
      Therefore, I recommend you to check out the Fuzzy Duplicate Finder tool. The add-in searches for partial duplicates that differ by 1 to 10 characters. It also detects omitted, excess or mistyped symbols.
      The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

  13. Hi,

    I'm using this formula currently: =IF(COUNTIF('Kansas City-CRs'!C:C,TAS!A5)>0,"YES","NO"), but it's returning YES if any portion of A5 is found in C:C. For instance in A5 if it says 'Very High Elevated LDL' but in C:C there is just 'Elevated LDL' it will return Yes. I want it to return YES only if it's an exact match. I tried doing match and index and exact formulas but can't get it to work. Any help would be much appreciated!!

    Theresa

  14. Hello!

    I am trying to build a report for to display whether a code is at risk or not.

    I have a tab for Data and a tab for Overview. On each tab there is a column for code and a column for risk (Y/N). I am looking for a formula that will take the Y/N indicator that is entered for a specific code (XYZ) on the Data tab and automatically fill into the coinciding row for code XYZ under the risk column on the overview tab. The codes on the Data tab are not in the same order as the Overview tab. Is this possible?

    Please let me know if there is any additional information needed.

  15. I am putting together an attendance calendar for my staff.

    One tab has the calendar, and the 2nd tab has a table of call-in dates and reason codes. I want to use conditional formatting to color the calendar based off the date AND the reason code. I am able to color in the calendar based off JUST the date by using the formula =NOT(ISNA(MATCH(B10,$G$6:$G18,0))) but I cannot seem to add the secondary condition - the reason code. I have tried the AND function, the XLOOKUP function, the IF/IFS function etc. and as soon as I add a second condition all the formatting goes away.

    Please let me know if there is more information you need to better understand the problem I'm having.

  16. Would you please help?
    I have multiple columns with values of prices of products and some contain "#N/a". I used if and function to compare the prices in all columns and give me "No" if there was no change in price and "yes" if there was a change, but this does not work for columns with #N/a values as it gives me "yes" as if there was a change in price but in fact there is not. How can I get the result I want while ignoring #N/a?

    Also what formula can I use to know if the price changed to be higher vs lower?

  17. Seeking support for solving the bellow result

    C1=MOHAMMAD, Osman Goni
    D1=Osman Goni
    The person is same but during data collection mismatch the spelling or missing any part of the name.
    So need expected result in E1 'True'
    Would you please anyone help to solve this issues. Thanks.

  18. This is similar but not the same as the situation posted above.
    I have a workbook with tabs for each week ("Week 1", "Week 2", etc...) and listed in column E are names and column G are a Yes or No for goal setting. The names are not in order on each sheet (for reasons). I want to setup a Standings sheet that shows how many times each person met their goals for each week.
    I found I can do this with an individual sheet with the following formula, but I can't figure out how to get it to check all the sheets.
    =COUNTIFS('Week 1'!E:E,"Will",'Week 1'!G:G, "Y")

    Thanks for any help.

  19. Hi there,

    I have 2 lists from the past 2 years that have email addresses of attendees for a course
    I need a formula that can make a list of emails that have been used both years to see if they would like to attend for the 3rd year running
    I have tried =IF(EXACT(H2:H970, R2:R1273), "Match", "Don't match" but obviously this only works if there are in the same order.
    One list has 970 emails the other has 1273.
    Ideally if it could produce a list of the emails that have occurred both years so then we can email that list rather than a true/false yes/no match/dont match.

    Hopefully that makes sense!
    Thank you

  20. Not sure if this will work. I have a list of learners and which training they took in 2023. I also have a column that shows the time spent on that training.
    I want to find the average time spent for each learner.

    So, I want to say, if the Learner is the same, calculate those duration columns (and then maybe calculate the average at the end)

    Learner A Training 1 0:04:00
    Learner A Training 2 0:13:00
    Learner A Training 3 0:20:00
    Learner B Training 1 0:14:00
    Learner B Training 2 0:09:00

    Thank you!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)