Compare multiple cells to see if they match exactly

I'm comparing 12 cells on different sheets (in the same workbook) and need to determine if they are all an exact match to one another. Basically, I want something like exact(), but I need to compare more than two strings. Any ideas?

Thanks!

You can use an AND() function to assemble the EXACT() functions... and overlap the checks.. if all return TRUE then the AND result will be TRUE and therefore all exactly alike

e.g.

=AND(EXACT(A1,B1),EXACT(B1,C1),EXACT(C1,D1),etc...)

replace the references with the different sheet references to compare.

i suggest a more flexible, simpler method is:

{=AND($A$1=$A$2:$A$12)}

Explanation:

A1=A2:A12

- this will return an array (meaning a list) of booleans (True/False), indicating which cells match the first cell, and which don't

AND
- does the same thing as shown in previous answers-- if all items in the list are True (ie match the first cell), then your final output is True. If any items in the list are False (ie do not match the first cell), then your final output is False.

{}
- the curlies are not typed-- when you enter the formula, press CTRL-SHIFT-ENTER instead of just ENTER, so it gets treated as an array-formula. This is necessary for the AND part to work right.

This thread was solved a long time ago; the OP hasn't logged on in over 5 years


{also for any future readers, Control + Shift + Enter for array formulas}

This thread was solved a long time ago; the OP hasn't logged on in over 5 years


{also for any future readers, Control + Shift + Enter for array formulas}

I may be misunderstanding, but i thought the purpose of this forum is to provide useful info to future readers, not just the OP.

This topic is still relevant. I found it while researching this topic. Office 2010 is still supported, and might still be the most widely installed version.

Also, my solution is different than the others, so it gives people another useful option.

http://www.techrepublic.com/blog/windows-and-office/poll-results-microsoft-office-2010-is-most-common-version/

Thx for catching my typo

John, thanks for that input

While speshul is correct that the thread is many years old and the OP may not have been back since then, all alternative answers are welcomed. I would point out that as long as you are applying that ARRAY to a smallish range, that is a good alternative. But if the range is large, excessive use of ARRAY formulas can start to slow your file down

dawn12789,Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.