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. Continue reading
by Alexander Frolov, updated on
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. Continue reading
Comments page 3. Total comments: 190
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
Hi! If I understand your task correctly, the following formula should work for you:
=INDEX(Sheet1!$C$2:$C$20,MATCH(1,(Sheet1!$A$2:$A$20=Sheet2!A2)*(Sheet1!$B$2:$B$20=Sheet2!B1),0))
For more information, please visit: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
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?
Sorry, I do not fully understand the task.
As it's currently written, it's hard to tell exactly what you're asking.
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..
Hi! To get the quantity or weight corresponding to a specific item, use the VLOOKUP function. Try to use the recommendations described in this guide: Vlookup from another sheet. I hope my advice will help you solve your task.
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.
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.
Hi! You can find the maximum value in the range using the MAX function. Then compare it with the target value using the IF function.
IF(A1>MAX(B1:B4), ... , ... )
This should solve your task.
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?
Hi! If I understand your task correctly, the following tutorial should help: How to find duplicates in Excel.
=COUNTIF($L$3:$L$1649, L3)>1
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.
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.
Hi! You can find a value from column A in column B and get the corresponding value from column C using the INDEX MATCH formula. For example:
=INDEX(C1:C20,MATCH(A1,B1:B20,0))
You can also use the XLOOKUP function for searching:
=XLOOKUP(A1,B1:B20,C1:C20)
Thanks you Alex for the reply, the INDEX MATCH seems to work for me, saved me lot of time.
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
Unfortunately, this information is not enough to give you any advice.
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
Hi! Excel formula cannot move the cursor. You need to use VBA.
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
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
Also, if the column is blank I need it read as zero dollars.
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!
Hi! I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:
=IF(AND($G8<40000,OR($B8="AZ",$B8="NM")),"N/A","N")
For more information, please see Excel IF with multiple AND/OR conditions.
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
Hi! If I got you right, the formula below will help you with your task:
=INDEX(B1:B10,MATCH(TRUE,ISNUMBER(SEARCH(C1,A1:A10)),0))
For more information, please read: How to find substring in Excel
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
Hi! You can find the address of the first number greater than 100 in column A using the formula:
=ADDRESS(MATCH(1,ISNUMBER(A:A)*(A:A>=100),0),1,4)
You can also find useful information in this article: Excel ADDRESS function to get cell address and more.
IF(Z814>=F814,F814)+AND(Z814<=F814,Z814) Not working both the condition whih is only get the firt condidition kinldy guide this one
Hi! Your formula is not written down correctly. I recommend that you study this manual: IF AND in Excel: nested formula, multiple statements, and more.
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.
Hello! If I understand your task correctly, our Merge Duplicates Wizard for Excel, may help you solve it in a few clicks. Select a column with duplicates and quickly merge duplicate rows into one without data loss. 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 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,
Hi! Write the formula =B55 in A1. Copy it to C1. Get =D55
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
My first answer based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
To get the values of B and C that matched the value of A, you can use the VLOOKUP function and these instructions: Excel VLOOKUP function tutorial with formula examples.
Hello! I have a question for you and hope you can help!
I am setting up a sheet to help with pricing.
I would like it set up so that I can enter a number of times that someone buys and in another cell it will calculate the total cost.
But the more you buy the different price point it would be.
so if buying 1 it's $2.50, buying 2 is $2.40...
I tried using an IF formula =IF(A1=1, C3; A1=2; C4) but that's not correct.. not sure what I need to do differently!
Hi! Look for the example formulas here: Nested IF in Excel – formula with multiple conditions or use IFS function.
I figured it out! I used an IFS function instead -
=IFS(E3="",0 ,E3=1, O10, E3=2,P10, E3>2,0)
Hi Alexander, I am really stuck on a formula for the following;
If i enter a value in C1, I want it to either permanently highlight or delete the matching value that is in C2:V21.
When i change and add a new value in C1, I need the existing and new value to still be highlighted in C2:V21.
Example:
I enter '9' in C1.
I need to permanently highlight or delete the '9' that is located in C2:V21.
When i change C1 to '23', I need to then have both 9 & 23 permanently highlighted or deleted from C2:V21 and so on.
Any assistance is appreciated.
Thank you
J
PS. this is for a non-profit.
Hi! With a formula, you cannot remove a value from other cells. You can highlight with a color the cells with a certain value if you use conditional formatting.
I have 2 columns. Column A with 6 different items with dropdown menu (Apple, Bag, Pen, Pillow, Shoe, Flower) and the other column B with Status from dropdown menu (Lost, Found, Destroyed and Fixed). I want to create a formula in order to count for example how many apples are found , how many apples are lost, how many are fixed. Same for different items. What formula can I use?
Hello! You can count the quantity of values across multiple criteria using the COUNTIFS function. For more detailed instructions, read here: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
Brilliant! Thank you so much Alexander!
One more question - I'm trying to find a formula to highlight the cell is the date is in 3 days so for example if today is 13th July all the cells for 14th, 15th and 16th July will highlight in red.
Hi! You can find the examples and detailed instructions here: How to conditionally format dates and time in Excel with formulas and inbuilt rules. You can select the cells if the date is in 3 days, for example with this conditional formatting formula: =A1-TODAY()<=3
I need to know if the value of one is A, B,C or D then take the total from collum E and put in collum F
how is that done?
Sorry, I do not fully understand the task.
As it's currently written, it's hard to tell exactly what you're asking.
I'm trying to work out this formula but haven't had any luck so far:
Column A with 'Order Numbers'.
Column B with 'Suppliers' (there's two of them).
Column C to return those Order Numbers that appear next to / match both suppliers in Column B. And the ones that don't appear to both Supplers - show blank cell.
Hi! If I understand your task correctly, try the following formula:
=IF(COUNTIF($A$1:$A$100, A1)=2,A1,"")
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
Hi Alexander, thank you. I tried it, it returns blanks only.
Here's a better example:
Column A (order number) | Column B (product) | Column C (result)
1 | 5001 | Apple | /blank/
2 | 5002 | Apple | 2
3 | 5002 | Apple | 3
4 | 5002 | Orange | 4
5 | 5002 | Orange | 5
6 | 5003 | Orange | /blank/
7 | 5004 | Apple | 7
8 | 5004 | Apple | 8
9 | 5004 | Orange | 9
10 | 5004 | Orange | 10
11 | 5005 | Apple | /blank/
12 | 5005 | Orange | /blank/
Column C I need to return those Line numbers where Order number appears next same product. In this examples this would be as shown above.
Hi! I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.
=IF(COUNTIFS($A$1:$A$100, A1,$B$1:$B$100,B1)=2,ROW(),"")
Hi,
Is it possible to match two different columns of data and return values based on matching contents? I have two separate sheets containing an ID number and a corresponding date. Sheet A contains three variables, a customer ID number, a date and time whereas Sheet B contains only selection of these ID numbers and a corresponding date.
I am hoping to match customers by their ID number and date of attendance from Sheet A to the matching values in Sheet B and if a match is detected to copy the time variable from Sheet A to the corresponding ID number and date in Sheet B, is this possible? I have tried combinations of IF, MATCH and INDEX functions but can't get to work as it's covering a range of customer ID's that may be in separate rows across the sheet. For Example ID Number 1 on 17/06/23 with a time of 12:00 could be in cell B1 in Sheet A but in B5 in Sheet B, can Excel check across a range of values for matches within another range?
Thanks in advance.
Hi! You can find the examples and detailed instructions here: Excel INDEX MATCH with multiple criteria - formula examples.
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.
=SUM(--(A1:B10=TRUE))=1
=COUNTIF(A1:B10,TRUE)=1
Hi all,
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.
Hello,
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.
Hi,
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.
Hello!
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.
=AND(ISNUMBER(MATCH(A1,$B$1:$B$100,0)),OFFSET(B1,MATCH(A1,$B$1:$B$100,0),0)=A2,OFFSET(B1,MATCH(A1,$B$1:$B$100,0)+1,0)=A3)
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
=ISNUMBER(SEARCH(TEXTJOIN(",",TRUE,A1:A3),TEXTJOIN(",",TRUE,B1:B100)))
Alexander,
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.
Hello!
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.
For example:
=IF(ISNUMBER(MATCH(Master!B2,Walmart!B2:B1000,0)),"Yes","")
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:
=IF(AND(NOT(ISBLANK(C3)),NOT(ISBLANK(D3)),C3=D3),1,0)
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.
Hi!
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...
Hi!
If I got you right, the formula below will help you with your task:
=IF((A1="Yes")*(B1="Yes"),"Yes","No")
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.
Hello!
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.
Hello!
The following tutorial should help: How to compare two columns in Excel for matches and differences.
For example,
=IF(COUNTIF($B:$B, $A2)=0, C1, "")
hello
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
example
Mussa Jayden Ally vs Musa Jayden Ally
I need to see these match regardless of the difference in the name mussa vs musa
Hello!
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.