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:
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:
As the result, you'll get TRUE if two cells are the same, FALSE otherwise:
- 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:
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 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, "")
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:
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, "")
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:
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")
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:
For example, to see if cells A2, B2 and C2 are equal, the formula is:
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.
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.
COUNTIF formula to check if multiple columns match
Another way to check for multiple matches is using the COUNTIF function in this form:
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:
If you are comparing a lot of columns, the COLUMNS function can get the cells' count (n) for you automatically:
And the IF function will help you return anything you want as an outcome:
=IF(COUNTIF(A2:C2, A2)=3, "All 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:
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:
In combination with IF, it takes this shape:
=IF(AND(EXACT(A2:C2, A2)), "Yes", "No")
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:
It's best to be used for checking 2 - 3 cells.
Excel 365 and Excel 2021 understand this syntax as well:
In Excel 2019 and lower, this should be entered as an array formula by pressing the Ctrl + Shift + Enter shortcut.
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)
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.
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")
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:
For example, to compare Matrix A in B3:F6 and Matrix B in B11:F14, the formula is:
To get Yes/No as the result, use the following IF AND combination:
=IF(AND(B3:F6=B11:F14), "Yes", "No")
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!
How to verify there’s only one Logic True Value in range of cells using combinations of OR and AND functions?
The formula will return TRUE if there is only one TRUE logical value in the range.
I am very stuck in a formula and would like some help. I have an export from a survey from Ms Forms, the answers to the survey have multiple options meaning that the excel report has multiple values in a cell divided with the symbol ";". We also have an option "other" which is free text choice. I need to build a calculator to automate the count of responses but i am not sure how to do that.
Hi! Split the answer values into separate cells. You can then count them. Use these instructions: Split string by delimiter or pattern, separate text and numbers. I hope I answered your question. If something is still unclear, please feel free to ask.
I have 2 spreadsheets of nearly identical information between them, except 2 columns. I want to update the 2nd sheet when remaining values match between the 2 spreadsheets. I know I can order them similarly and then simply copy the column in question from 1 sheet to the 2nd, but I would rather have the accuracy of a formula.
Hello! Replacing an old value in a cell with a new value is not possible with a formula. This can be done with a VBA macro. I also recommend to pay attention to the Copy Sheets tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I was given this really difficult assignment and cannot seem to figure it out. On one sheet I have a matrix that consists of one column for lane numbers (ex. 1000) and two other columns for ranges of volume (ex. 0 to 50, 50 to 100). On another sheet I have the same lane number (1000) but different amounts of randomized volumes. How can I formulate so that it matches the same lane number from the matrix tab and counts how many times it fell in between the two ranges?
I would greatly appreciate the help!!
Hi! You can count the number of values that match multiple criteria with the COUNTIFS function. Use this guide with examples: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria. I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
This helpful page explains ways to test if a cell matches any cell in a range, also to test if two ranges are equal. I'm trying to extend these methods to test if a range of three numbers in adjacent cells of column A matches a three-adjacent-number range found anywhere in column B which contains many such three-number ranges. For example, if column A contains (only) 8, 6, and 2 I want to search column B to see if the 8,6,2 sequence is present. Any suggestions would be appreciated. I'm using an older Excel version.
Find the first match of A1 using the MATCH function. Use the OFFSET function to take two values below and compare them to A2 and A3. Use the AND function to combine the three conditions.
If you have the TEXTJOIN function, you can combine the numbers into a text string and use the search as described in this manual: How to find substring in Excel
Thank you for your quick response and suggestion to use the OFFSET and MATCH functions to solve my problem. The specific code you provided does not quite solve the problem because it won't find the matching three-adjacent-number range if an earlier three-adjacent-number range contains the first digit of the search triplet. Using my prior example, if A1:A3 contain 8,6,2 and if B9:B11 contain 7,6,8 followed by 8,6,2 in B13:B15, the result should be TRUE because the B13:B15 numbers match. But the result is given as FALSE because the lower triplet isn't found. So far I have not found the right combination of OFFSET, MATCH, and AND functions to get around this limitation.
Hi! I don't think you can solve this problem with an old version of Excel. Unfortunately, the functions you need are not available to you.
OK, thanks for that info. I have a workaround that may be close enough for my purposes. Since I can't match arrays in my older Excel version, I will have Excel compute a single number for each three-adjacent-number item using a mathematical formula that combines all three numbers. Then a single-number MATCH function similar to what you suggested will let me find the cases I want. I can tolerate some small number of false positive matches which could result if the mathematical formula is too simplistic.
Is there a way to match approximate text? Let's say I'm comparing company names and cell A1 says "Clothing Corporation" and A2 says "Clothing Corporatio" (missing the final "n"), is there a fairly easy way to do that? It won't always be 2 words - often times 3+ words in the company name.
You can find a formula that will help you find partial matches between text strings in this guide: How to find substring in Excel.
I'd recommend you to have a look at our Fuzzy Duplicate Finder that can help you find and fix typos and misprints in your worksheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hello, I have a workbook where our organization tracks gift cards we distribute to clients. The serial numbers are entered into a worksheet based on on the card's vendor/donor (e.g. one worksheet for Walmart cards, another for Kroger cards). The workbook also includes a Master Worksheet that contains all the serial numbers for all the gift cards, entered when we receive the cards. I would like to create a formula that marks the gift card as distrubuted in the Master Worksheet when the serial number is entered in its corresponding Worksheet when we distribute it.
For example, I have one worksheet with all Walmart gift card records. When we hand out a gift card, the employee records the serial number. Would it be possible to create a formula in the Master worksheet, so that when a serial number in the Walmart worksheet matches a serial number in the Master Worksheet, the "Distributed" column reads "Yes"?
Hi! Look up the serial number from the Master worksheet on the Walmart worksheet using the MATCH function. You can find the examples and detailed instructions here: How to use MATCH function in Excel.
The ISNUMBER function will return TRUE if a match is found. I hope it’ll be helpful.
Hi, I’m wanting to return a value if one cell equals another then put in say 10, otherwise leave blank. I have tried =if (d3=c3,10,””). If you leave both those cells blank it returns the 10 value which is not what I want. I want it to be blank but if those two cells are equal then I want the answer to be 10. Any help would be greatly appreciated.
To check if a cell is empty, you can use the function ISBLANK. For more information, please visit: ISBLANK function in Excel to check if cell is blank. Use the IF AND statement for multiple conditions.
If I understand your task correctly, try the following formula:
Alexander, thank you so much it worked perfectly.
If cell 1 ,2 & 3 have values and are populated accordingly, then I want a Text string returned, for example "Matched" in a new column.
If cell 1 ,2 & 3 have no values and are not populated accordingly, then I want a Text string returned, for example "Unmatched" in a new column.
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, explain what "populated accordingly" means.
I find a lot of this so informational! I have been scratching my head, but i'm trying to create a cell where if yes that Cell A says "Yes" based on a condition being met, and Cell B also says "Yes" on its own condition being met, that this formula will then read "Yes" indicating both conditions were met. Same if only one says YES and the other says No, this will read "no" and they must both be "YES" to end up as a yes? The issue i have is, if both cells conditions were not met and it says "no" the final cell is still indicating "true" since they match each other...
If that made sense to anyone...
If I got you right, the formula below will help you with your task:
Wow... I am just speechless, Alexander. You are extremely skilled at this, and understand it so phenomenally! I can't thank you enough for that!
I am comparing two columns and the individual columns have duplicated values. How can I match ? for example if one of the column have two 10,000 and the other column have three 10,000 I want to get one 10,000 as a difference.
I am working a pulling info and I was using vlookup and it work great =VLOOKUP(A2,'Employee Ben'!A$2:DE$1650,107,FALSE)
I can reference the ssn in the first column and then it searches the employee ben sheet for the number and then pulls the column that i ask for for the match. Problem is need to match 2 fields in the same row and and then pull a specified column. I cannot figure this out. Driving me Mad.
To Vlookup multiple criteria, you can use either an INDEX MATCH combination with multiple criteria or the XLOOKUP function.
Is there a way to look at all (text) data in Column A, does it match a text value in Column B, and if 'yes' - enter what is in Column C into Column D.
Column B, C and D are all aligned in the same row.
The following tutorial should help: How to compare two columns in Excel for matches and differences.
=IF(COUNTIF($B:$B, $A2)=0, C1, "")
I want to ask if there is a possibility of compare the names in two rows where by in each row there are three for each person and the names but some of the names are not match exactly. If there is a possibility that at least two names to be match and the status became match
Mussa Jayden Ally vs Musa Jayden Ally
I need to see these match regardless of the difference in the name mussa vs musa
You can split each text into 3 columns as described in this guide: How to split cells in Excel. Then compare the columns using these guidelines: Compare two columns for matches and differences.