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. Please could someone assist.. its not very complex however I am struggling.
    I am attempting to get excel to return a bonus amount in a specific cell if the 2 figures match the table

    So if C17 and D17 match the exact combination of the table S13:T27 then it should return the correct amount matched with the table U13:U27

    Ie if C17 states 400 D17 states 600
    The table would have variations of

    S T U
    NIL / 400 = Nil
    400/ 400 = 400
    400 / Nil = Nil
    400/ 600= 400
    600 / Nil = Nil
    600/ 400 = 400
    600/600 = 600

    It would need to return 400
    If nil/ 400 needs to return nil
    Etc

    Thank you in advance

  2. I am trying to figure out how to do this: If A2 and B2 combined of sheet 1 are the same as any instance in column A2:A2630 and B:1:AT:1 combined, then it will return the data found in cell C2 of sheet 1 in cell B2 of sheet 2.

    Sheet 1
    Code State Answer
    123456 Alabama Exempt
    123457 Alabama Taxable
    123458 Alabama Exempt
    123459 Alabama Exempt
    123460 Alabama Taxable
    123461 Alabama Taxable
    123462 Alabama Taxable
    123463 Alabama Taxable
    123464 Alabama Taxable
    123465 Alabama Taxable
    123466 Alabama Taxable
    123467 Alabama Taxable

    Sheet 2

    Avacode Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware District Of Columbia Florida Georgia Hawaii
    123467
    123466
    123465
    123464
    123463
    123462
    123461
    123460
    123459
    123458
    123457
    123456

  3. ITEM W ITEM X ITEM Y result
    a g 10 13
    b c apple 18
    c d 12 apple
    d a 13 12
    e b 18 cup
    f e cup 20
    g f 20 10

    i want to match as a=a=13, b=b=18, c=c=apple, d=d=12, e=e=cup, f=f=20, g=g=10.. can you suggest the formula?

  4. i have two sheet with items and its qty and items and its weight.. both sheet items are same but not in same order. i need to match these items from these two sheets and get the value of each items either qty or weight. so my final sheet shoud show ITEMS - WEIGHT - QTY..

  5. Hi I need to help with situation:
    I have row where I write Yes/no Let´s say R2:AE2 and to the D cell I neet to write something like: If all cells in row R2:AE2 are YES put there yes, if all are NO put NO if there is YES and NO leave it empty.
    Any ideas??
    Thank you!

    • Hi! Use the COUNTIF function to count the number of cells with the text "Yes" or "No". Use the COLUMN function to count the number of cells in a string. Use the nested IF function for multiple conditions.
      The formula might look like this:

      =IF(COUNTIF(R2:V2,"Yes")=COLUMNS(R2:V2),"Yes", IF(COUNTIF(R2:V2,"No")=COLUMNS(R2:V2),"No",""))

      This should solve your task.

  6. Hi,
    I need a formula to check if one cell is greater than 3 other cells, then apply a specific text if this condition is met.

    For Example i have the Following Range: 25 50 75 100 (This range could change depending on the pre conditions)

    I want to write a formula that checks which is the highest value in the above range and returns a specific narration according to the highest value in the range.

    Thanks in advance.

  7. What if you are trying to determine if Column L lists one client multiple times? I want a Yes/No return in Column Z. Currently my formula is: =IF(OR(L7=$L$3:$L$1649),"Yes","No")

    the problem here is that L7 shouldn't be included in the range BUT I want all numbers before and after...I want the range to include L3:L6 AND L8:L1649.

    The problem is if I separate those two out into the following formula, it wont work as it comes up with a #SPILL error:

    =IF(OR(L7=$L$3:L6)&(L7=L8:$L$1649),"Yes","No")

    Any idea how to get each Row in Column Z to return an accurate Yes/No based on whether the name of the company in Column L is used in/the same as any other Row in Column L?

  8. I'm having a bit of a problem here :D
    What if I had four sets of colums A,B,C,D
    Column C consists of same letter+digit strings as column A, but columns C has them sorted not in the same order. Then column D consists of dates assigned to the strings in column C.
    How can I write formula that will check if column C has a cell with string of letters+numbers that matches a cell in column A - then if it's a match, column B will download the date from a corresponding cell (to cell in C column) in column D

    • Hi! If I understand correctly, to find the value in column C and get the corresponding value from column D, you can use INDEX MATCH function. Here is an example formula:

      =INDEX(D1:D10,MATCH(A1,C1:C10,0))

      I hope it’ll be helpful.

  9. There are 3 columns: A, B, C

    If value of a cell of A match with any of the cell value of B column, I want to print value of C [match index of B] in result.

    Is it possible?

    Thanks in advance.

      • Thanks you Alex for the reply, the INDEX MATCH seems to work for me, saved me lot of time.

  10. I have data in two sheets June and July with ID numbers and values in two different column. I wanna bring July yearly values in June sheet.
    Some ID's are similar number or duplicate in ID coloumn so cant use VLOOKUP or sumifs .

    Pls help

  11. i need help for this function: if column A has counting 1 to 5 and column B is empty and i put random number in column B. if entered number match with column A then then ok otherwise system move cursor where entered number exist

  12. How do match with formula in excel
    A1=P b1=D C1=as D1=cd
    A2=as b2=cd
    Result E1 CELL IN TRUE

    C1 AND D1 cell same value match in column A & B

  13. Alexander, Thank you for your help on this formula =IF(AND($G8<40000,OR($B8="AZ",$B8="NM")),"N/A","N"). I will clarify (it won't let me snippet a picture).

    Column B
    State Which is either AZ, CO, KS, NM, WY

    Column G-P&C
    Amounts ranging from -$168 to $500,000+

    Column H-Life
    Amounts ranging -$ numbers to $0 to blank to $100,000+

    Column J
    P&C Goal
    $40,000 for AZ, KS, NM-Formula to return Y,N
    N/A for CO, WY and Formula to return N/A
    Need this formula based on column B and G

    Column K
    Life Goal
    $4,000 for AZ, CO, KS, NM, WY
    Need formula based on column B and column H

  14. Also, if the column is blank I need it read as zero dollars.

  15. I have column B which indicates a state of AZ, CO, NM, WY. Column G indicates an amount in dollars. States of AZ and NM have a goal of $40,000 and CO and WY do not have the goal so I want to return N/A (not applicable). I am struggling with the formula. Nothing I am doing is returning a result =if(($g8<$40,000)AND($b8="AZ",$b8="NM"),"N/A","N")) or =IF(($B8="AZ")*OR($B8="NM")*AND($G8<40,0),"N/A","Y"). Yikes, I am in over my head!

  16. Hello! I am using the below formulate to find one cell's value in a column range but it is not working because the cells in that column range contain multiple values separated by spaces ;

    = IFERROR(INDEX($B:$B,MATCH(C1,$A:$A,0)),0)

    example - looking to match c1 value of 123 to a cell in column A and return the value next to the matching cell in column B - the problem is that the values in column A appear as such; "123 234 655"

    so the 123 is contained in cell A but because it has other values it returns 0

  17. Hi,

    I have multiple column where the value are mentioned. I need to identify the cell reference where the value of the cell is >=100.
    For example cell a3 = 0 , a4 = 70, a5 = 99,...a30 = 101

    Then through index match i will be able to identify the cell reference as A30.
    Plese help getting this figured out

    Regards
    Iyer

  18. IF(Z814>=F814,F814)+AND(Z814<=F814,Z814) Not working both the condition whih is only get the firt condidition kinldy guide this one

  19. Hello,

    Wondering if you're able to help me. If two cells match within the same column (cannot be case sensitive), is there a way to copy the data from some of the cells in the row of one match to the row of the other? Hoping you can help me. I have 40,000 columns containing duplicates! Thanks so much.

  20. Hello There I have a question here would help please?

    I use Office 365
    I need to make a formula that in case (for Example) A1= one cell of range (B2:B100) then C1 equal same cell number of range (D2:D100)

    I mean in case A1=B55 then C1=D55
    A90=B30 then C90=D30 --- but B column is a range of 100 cells and D is a range of 100 cells too
    like that - I'm collecting information from many tables so,

      • Noooo it's not like that -- Look,
        I have 2000 items in A column ---> i have first specifications in B column -----> i have the second specification in C column (all specifications for all the items 2000 each on in same raw but different columns).

        Now in a new table i need to create dropdown list for the whole 2000 items to be once i choose any of those 2000 items in A1 -- first specification appears automatically in B1 and second specification appears automatically in C!1

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 :)