• Status: Solved
• Priority: Medium
• Security: Public
• Views: 387
• Last Modified:

# Formula needed that indicates when two other cells are identical.

I have a spreadsheet I use to match up lists of names buildings or long lists of data that can be thousands of lines long. I need a cell to indicate when two other cells are identical. I am currently using the following formula: =IF(A3=D3, "Match", "No Match")

Unfortunately if I need to add or delete cells from column D (or vice versa)  then the formulas from that row and below no longer match up, leaving me to drag the correct formula above through to the end in order to rectify the error.

What formula can I use that will not need this constant adjustment and allow me to delete cells or add cells as I need? Please no visual basic or pivot tables if possible; hopefully this can be done with just a formula.
0
Humbl3St3ps
Asked:
• 6
• 5
• 4
• +2
1 Solution

Commented:
Your asking the formula to be dynamic in such a way where you are hitting the boundary between formulas and vba...if that formula exists I would like to see it..it would be very lenghty indeed...can you post your present formula now?
0

Author Commented:
My present formula has already been posted. Please look at end of first paragraph on original question submission.
0

Commented:
Use this

=IF(A3=OFFSET(A3,0,3,1,1), "Match", "No Match")

Sid
0

Commented:
Please ensure that the formula is not in E3 else when you delete Col D, Col E will move into Col D and you will get a Circular Reference Error.

Sid
0

Commented:
Use a formula with COUNTIF() instead, like this:

=IF(COUNTIF(\$A\$2:\$A\$44;D2)=1,"Match","No Match")

Please take a look at the attached file.

jppinto
Countif.xlsx
0

Author Commented:
SiddharthRout,

As suggested in the original post by writing "or vice versa" in parenthesis, I need to be able to manipulate both Columns. The formula you suggested works perfectly for adding and deleting cells in the D Column. Unfortunately it does not allow me to delete or add cells in the A Column without producing "#REF!" in the cell containing the formula. I've not used the OFFSET formula before and so after a bit of internet study I can see where I can use it in other places. Thank you for the suggestion.

Dusty
0

Commented:
>>>"or vice versa"

Not sure if you can do that without VBA but I could be wrong :)

Sid
0

Commented:
Dusty, did you checked my proposal?
0

Author Commented:
Jppinto,

I cut and paste your formula, but get an error when I hit enter. I've changed some of the numbers around, but I'm not sure what will correct the error.

Dusty
0

EngineerCommented:
Try this formula

=IF(INDIRECT(ADDRESS(ROW(),1))=INDIRECT(ADDRESS(ROW(),3)), "Match", "No Match")
0

Commented:
If you want it "vice-versa", then you can change the formula to this:

=IF(AND(COUNTIF(\$A\$2:\$A\$44,D2)=1,COUNTIF(\$D\$2:\$D\$44,A2)=1),"Match","No Match")

Deppending on your Regional Settings, you should change the "," by ";" making my original formula look like this:

=IF(COUNTIF(\$A\$2:\$A\$44;D2)=1;"Match";"No Match")

jppinto
0

Commented:
ssaqibh Bhai: Awesome :)

Sid
0

EngineerCommented:
Thanks.....if it is acceptable :)
0

EngineerCommented:
small mistake.... the 3 should have been 4
0

Commented:
ssaqibh Bhai: I guess you meant 4 instead of 3 in your formula?
0

Commented:
Xover

Sid
0

Author Commented:
ssaqibh,

I've given your formula a thorough beating after the quick correction to "4" and it has passed every test. I'm actually comparing multiple columns with each other, and I was able to adapt your formula to account for the comparisons with the other columns and everything is working perfectly. Thank you for your time, effort and brilliance. They are well appreciated, thank you.

Dusty
0

EngineerCommented:
Thanks for the points and the grade.

Just a quick advice for the future. You should have chosen my first comment as an answer because that is the one which contains a solution. The other comment was merely an adjustment.

Saqib
0

Author Commented:
ssaqibh,

Advice noted and will be followed in the future. Thanks again.

Dusty
0

## Featured Post

• 6
• 5
• 4
• +2
Tackle projects and never again get stuck behind a technical roadblock.