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:
=A2=B2
As the result, you'll get TRUE if two cells are the same, FALSE otherwise:
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:
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:
=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.
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:
=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", "")
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:
=AND(EXACT(A2:C2, A2))
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:
OR function
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.
COUNTIF function
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.
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:
=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")
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!
179 comments
Hello Alexander! your formulas works perfectly!!!
I need your support again.
i need to write couple of IF conditions
A B C D
type quantity Unit Solution
Riba 1 CT
Meso 3 CT
Baklava 5 PAC
Pita 7 CT
Torta 8 PAC
If "Type" is Riba and "Unit" is CT, then multiply by 3
If "Type" is Meso and "Unit" is CT, then multiply by 4
Otherwise everything else should be as it is
Thanks!
Solved :)
Hello Ema!
You can find the answer to your question in this article: Excel Nested IF statements - examples, best practices and alternatives.
Good morning,
I have a scenario where I need to compare two fields to test "true" or "false", which is easy. I only want to compare the fields when both cells have values. If one of the fields is blank then the test should not be performed. I have tried multiple formulas and none work. Can you assist?
Thank you,
Hello Karrie!
Add one more condition to your formula:
NOT(OR(ISBLANK(A1),ISBLANK(B1)))
For more information, please visit: ISBLANK function in Excel to check if cell is blank.
I have this formula
=SUNIF(b2:b5,”a”,d2:d5)
I wish to add a 3rd column a2:a5 “py”
The result is in d15
Hello Paul!
If my understanding is correct, try to use SUMIFS function for two conditions:
=SUMIFS(D2:D5, B2:B5,”a”,A2:A5,“py”)
I want to write a formula as follows:
if cell j9 is equal to or less than c6,
if equal or less than result is c5+2,
if more than J9 the result is c5 +1
My first thought on formula is =if(AND(C6=J9,C6<j9)),(=sum(C5+2)),(=sum(C5+1))
This doesnt work, can you help?
Hello Howard!
You can find the answer to your question in this article: Nested IF in Excel – formula with multiple conditions.
I have a data, which i would like to arrive completion, Not started or WIP status for each group of activities.
EX:
Activity --- Activity#---status
Posting--- Activity#1--- Inprogress
Posting--- Activity#2--- Completed
Posting--- Activity#3--- Completed
Reconciliation---Activity#4--- Completed
Reconciliation--- Activity#5--- Completed
Reconciliation---Activity#6--- Completed
Followup---Activity#7--- Not started
Followup---Activity#8--- Not started
Followup---Activity#9--- Not started
JE---Activity#10--- NOt applicable
JE---Activity#11--- Completed
JE---Activity#12--- Completed
For above data, based on Activity type. would like to consolidate the data.
Posting - overall status: inprogess
Reconciliation - Overall status: Completed
Followup - Overall status: Not started
JE- overall status completed
if all activity status is Completed/Not started - Output expected Completed/Not stated, if mix of activity then WIP. in case of Not applicable it should ignored.
Can you please suggest what would be best approach or formula
Hello Alex,
I am currently trying to have a cell populate based on what its adjacent cell is showing.
For example, in my tab called SWMS, Column B is data validated and is set to list. This list pulls from data that I put into a tab called values in column B. In column A of tab Values, I have numbers in ascending order, where each cell in column B is having a different next to it in column A.
Column C in tab SWMS needs to have the number adjacent to its cell in column B, and hence I believe Column C needs to have an IF formula, however I need to know what this formula needs to be.
Essentially, in the SWMS tab, column B will display a document title (taken form the values tab in column B) and next to it in column C the corresponding number needs to appear next to it automatically.
Can you please help me out on this?
Hello Dennis!
I’m not sure, I got you right since the description you provided is not entirely clear. If you want to find a specific value in a column and extract the corresponding value from another column, use one of these instructions: Excel VLOOKUP function tutorial with formula examples or INDEX & MATCH in Excel - better alternative to VLOOKUP or Excel XLOOKUP function with formula examples.
I hope it’ll be helpful.
Hi Alex,
Thanks for your quick response. That almost worked but not quite. Ill try explain it better this time.
Basically what I want is whenever I select an item out of a list in column B (those cells are data validated, and extracting data from another sheet), I want the adjacent cells in column C to automatically correspond with the correct item number.
The data in column B are document titles, and the values in column C would have to be their corresponding document numbers, which should automatically switch to the correct number depending on what I select in its adjacent cell form the list in column B.
I hope that makes it a little clearer with what I want.
Could you please have a look at this again and try help me?
Kind Regards,
Hello Dennis!
If you have a list of documents in which each document title corresponds to a number, you can use the links to the instructions that I have given you to find the number by the title. Write the formula for the search in the C column of the table.
Hi Alex,
Would you please suggest me the formula for the following criteria?
For the following table, product ID in range, in which Product ID 'A01' meet the condition as "Yes" to all the iteration for the Product ID 'A01' in Result received column will return the value "Yes" to All Product result received column for Product ID 'A01' as displayed below:
ProductID, Result received, All Batches result received
A01, Yes, --> No
A02, Yes, --> Yes
A03, Yes, --> Yes
A01, Yes, --> No
A04, Yes, --> Yes
A01, No, --> No
A02, Yes, --> Yes
Thank you,
Mrunal
Hi! Use COUNTIF function to count the number of values that have a specific product ID in column A. Use COUNTIFS to count the number of values under two conditions: the specific product ID in column A and "Yes" in column B.
If the results are equal, use the IF function to return "Yes".
Based on this information, the formula could be as follows:
=IF(COUNTIF($A$2:$A$10,A2) = COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"yes"),"Yes","No")
Can't thank you enough! Alex. It actually works. Much appreciated your help.
Hi, I have a list in columns A and B that are job codes and departments. Columns C,D,E,F,G have tasks/responsibilities. If I am given someone's job code and department in cells H and I, is there an equation where I output whether or not they are assigned tasks into J,K,L,M,N? For example having a cell in row A4 and B4 match cells in H1 and I1, output Cells C4, D4, E4, F4, G4 into J1, K1, L1, M1, N1?
And column H and I would be a list of employees, not just one job code and one department
Hello John!
If I understand your task correctly, the formula might look something like this:
=INDEX(C2:G8,MATCH(1,(A2:A8=H1)*(B2:B8=I1),0),{1,2,3,4,5})
You can find the examples and detailed instructions here: Excel INDEX MATCH with multiple criteria.
You can also use the FILTER function to get an array of values by condition. Try to use the recommendations described in this article: Excel FILTER function - dynamic filtering with formulas.
=FILTER(C2:G8,(A2:A8=H1)*(B2:B8=I1))
Hi Alex,
I am working off of two tables in excel. One is a data report that is pulling in performance metrics and the other is table that pulls in the same metrics and the same columns but they are benchmarks I want to compare the data too. The benchmark table is smaller and isn't one to one with the data. Now, I've done a match formula to determine if a row of text cells on the data table match of the same text cells in a row on the benchmarks table using the below formula:
=IF(ISNUMBER(MATCH($I2&J2&K2,$O$2:$O$109&$P$2:$P$109&$Q$2:$Q$109,0)),"Yes","No")
What I want to know is if I can match of a row of cells from the data table to the benchmark table, can I take the metrics associated with that row I pulled from the benchmark table and determine if its greater than or less than the metrics associated in the row of my data set?
Let me know if that doesn't make sense
Hi! If I understand your task correctly, the formula might look something like this:
=IF(ISNUMBER(MATCH(TRUE, O2:O4&P2:P4&Q2:Q4=I2&J2&K2,0)),"yes","no")
The following tutorial should help: Excel MATCH function with formula examples.
Thanks for the response!
So if there is a row match on the O, P, and Q columns with the I, J, K columns.. is there way to determine if the R column is greater than the L column?
Hi! It would be very good if you asked your question in full at once. Add one more condition to the MATCH formula. For example:
=IF(ISNUMBER(MATCH(1, (O2:O4&P2:P4&Q2:Q4=I2&J2&K2) * (R2:R4>L2:L4),0)),"yes","no")
Look for the example formulas here: Excel INDEX MATCH with multiple criteria - formula examples
Hi Alex,
Thank you very much for this formula! Its perfect:)
Is there a way to filter this scenario? I have 7 columns and they will each show either YES or be blank.
I want the 8th result column to reflect "COMPLETE" only if all of the boxes are equal and reflect YES. If any of the boxes are blank, or all of the boxes across the row are blank, I do not want the result YES to reflect.
My issue is that when trying to do an all match IF function, they rows that all the boxes are blank also are pulling into the result. I do not want those all blank rows to pull as complete.
Any help would be appreciated! Thanks!
Hi! If I understand your task correctly, the following formula should work for you:
=IF(PRODUCT(--(A2:G2="Yes")),"COMPLETE","")
A mathematical operation turns logical expressions into numbers. If the product of these numbers is 1, this is TRUE for the IF function.
Hello,
I would like to use the following IF formula to confirm if two cells are equal/ balanced, however, it is not working:
=IF(C7=E7,"OK","NOT BALANCED")
C7 and E7 are cells which both contain different formulas, based on other cells, but their results equal the same result, $9.40.
Formula in C7: =B7-A7
Formula in E7: =SUM(F9+F15)
The results returned for both of the formulas in cell C7 and E7 is $9.40 so the IF function should return as "OK" but instead, it returns as "NOT BALANCED".
What am I doing wrong here? Is it possible the IF function is considering the formula in the cells rather than the actual results of $9.40? If that is the case, how can I fix it?
I appreciate any help!
Hi! The value displayed in a cell is not always the actual value returned by the formula. You can see the actual value if you set the cell format to General. In your formula, try using the rounding function. The formula might look like this:
=IF(ROUND(C7,2)=ROUND(E7,2),"OK","NOT BALANCED")
Hello,
This worked, thank you so much!
I need to check if the number in column G and same then bring all the values from column N and if from those values bring the ones which are different values.
for example :
G18, G20, G21, G32, G33 are
50242-0135-01
50242-0135-01
50242-0135-01
50242-0135-01
50242-0135-01
and corresponding values for them in O are:
11
11
4
11
11
then I want if all the values in O are 11 then bring the different one associated with it. like in this example 4,
what can we use for this?
If a value is written in a cell, you can change it either manually or with the help of VBA macro.
I am finding your tutorials very useful. Thanks.
I have a specific query that I am sure you will be able to solve.
I have a date in say B2
I have a value in say B20
I have a list of all dates in a single column in another sheet (with nothing else)
I want to place the value of B20 in a cell to the right of the date in the seperate sheet that matches B2
Is this easily possible?
Hi! To write a value into a cell based on a condition, you can use the IF function. If I understand your task correctly, the formula might look something like this:
=IF(A1:A10=Sheet1!$B$2,Sheet1!$B$20,"")
HI, In sheet 1: in column 'B' there are dates, in column 'E' there are tag no. and in column 'I' there are frequency like year or month.
what i want is in sheet 2: column 'A' tag no are written if that tag no. matches with Sheet 1 Column 'E' tag no. and the frequency is year then only "DONE" should show in column 'B' of Sheet 2 or else show "Not Done".
Can anyone tell me which function to use?
Hi! To compare two columns on two worksheets, you can use the MATCH function. The ISNUMBER function will return TRUE if a match is found in two columns. Based on your information, the formula might look something like this:
=IF(ISNUMBER(MATCH(1,(Sheet1!E1:E10=Sheet2!A1)*(Sheet1!I1:I10="Y"),0)),"Done","Not")
Still not working
I will clear my doubt again
In sheet 1:
column 'B' there are dates,
column 'E' there are tag no.
column 'I' there are frequency like year or month.
column 'F' there are Dates
what i want is in sheet 2:
column 'A' tag no are written if that tag no. matches with Sheet 1 Column 'E' tag no. and the frequency is year then only "DONE" should show in column 'B' of Sheet 2 or else show "Not Done" and and in column 'C' the same date should come of Column 'F' of sheet 1
I have Added the date column this time.
Hi! I’m sorry, but your description doesn’t give me a complete understanding of your task. If this is not what you want to see in Column B of Sheet 2, then describe the problem in a way that makes sense to more than just yourself.
To understand what you want to do, give an example of the source data and the expected result.
I will clear my doubt again
In sheet 1:
column 'B' there are dates,
column 'E' there are tag no.
column 'I' there are frequency like year or month.
column 'F' there are Dates
what i want is in sheet 2:
column 'A' tag no are written if that tag no. matches with Sheet 1 Column 'E' tag no. and the frequency is year then only "DONE" should show in column 'B' of Sheet 2 or else show "Not Done" and and in column 'C' the same date should come of Column 'F' of sheet 1
The formula I sent to you was created based on the description you provided in your request. Here is the formula to get the date in column C. Or see the previous answer.
=IFERROR(INDEX(Sheet1!$F$1:$F$10, MATCH(1,(Sheet1!$E$1:$E$10=Sheet2!A1)*(Sheet1!$I$1:$I$10="Y"),0)),"")
This formula uses the instruction: Excel INDEX MATCH with multiple criteria - formula examples.
Hello.
I'm having a range of numbers and I want to know if there are 2 equal numbers. If there are 2 equal numbers, I want to be written "equal numbers" and if there aren't equal numbers to be written "without equal numbers". But, the range is between same cells except the cell I want to be related to.
As an example, I have the range B2-B11. I managed to get the first cell with the next function =IF(COUNTIF(B3:B11;B2)>0; "equal numbers"; "without equal numbers"), but the next cell is B3 and I can do the formula IF(COUNTIF(B4:B11;B3)>0; "equal numbers"; "without equal numbers") but I want to add the B2 cell there too. How can I add it, without "touching" the B3 cell, and so on?
Hello! Make sure you use an absolute reference to the range of numbers in the formula. Also, since there must be one countable value in the range, replace 0 with 1.
=IF(COUNTIF($B$2:$B$11,B2)>1, "equal numbers", "without equal numbers")
Column A - Column B
April 20, 2024 - John
April 20, 2024 - Brian
April 20, 2024 - Luc
April 20, 2024 - Ryan
April 20, 2024 - Sarah
April 20, 2024 - Emma
April 22, 2024 - John
April 22, 2024 - Brian
April 22, 2024 - Luc
April 22, 2024 - Ryan
April 22, 2024 - Sarah
April 22, 2024 - Emma
Column C and D have the two dates above listed in each cell (there are more dates all the way down until end of the month with the same people repeating in them same sequence) and each name is listed on a different row. I need a YES or NO answer. Yes if the person and date match from the list above.
Hi! If I understand your task correctly, these articles may be helpful: How to highlight duplicate cells and rows in Excel and How to find duplicates in Excel: identify, highlight, count, filter.
The formula might look like this:
=IF(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)>1, "Yes", "No")
I have two sheets Data and Attendance, with a mix of text and numbers, basically I'm trying to match a student ID, with the type of workshop they attended and if both are true then to return the text cell value in the appropriate row which is Y, N or blank.
The Student ID is in column 1 on both sheets.
I would like to check if Attendance A2 = Data A:A (these are both numbers) and Attendance G1 = Data D:D (these are both text) then I would like to return the text data in Data L:L
Data Sheet
A = Student ID (8 numerical digits)
D = Skill e.g. MH BC
L = Attended Y, N, Blank
Attendance Sheet
A = Student ID (8 numerical digits)
G1 = text MH BC
G2 is cell I want Y, N or to stay blank to appear in.
Hope this makes sense
Hello! Try to use the recommendations described in this article: Excel INDEX MATCH with multiple criteria - formula examples. For example:
=INDEX('Data Sheet'!L1:L10,MATCH(1,('Data Sheet'!A1:A10=A1)*('Data Sheet'!D1:D10=G1),0))
You can also find useful information in this article: Excel XLOOKUP with multiple criteria.
The formula might look like this:
=XLOOKUP(1,('Data Sheet'!A1:A10=A1)*('Data Sheet'!D1:D10=G1),'Data Sheet'!L1:L10)