Need to compare multiple cells to find if they match exactly

[Cyali]

Hi, I'm trying to compare values in multiple cells to see if they are all the same. If they are, I want the cell containing the compare formula to be blank. If there is at least one that is different, I want the cell containing the formula to write "Mismatch".

Usually I'd have no problem doing this with a combination of EXACT and AND, but I want blank cells to completely be ignored.

Is there any way to do what I need to without a long series of IF statements?

[DGagnon]

can you provide an example sheet?

[Cyali]

I put the formula I'm using in the Mismatch column. Currently I'm comparing two columns, but I need to be able to compare as many as needed.

And an additional note, in the original spreadsheet the columns being compared are not right next to each other as in the example below. And only the line for Item1 should be returned as a mismatch.


Example.xlsx

[DGagnon]

try using this you just need to put the columns in the count ranges

=IF(COUNTIF(C3:F3,C3)=COUNTA(C3:F3),"","Mismatch")

[Cyali]

Is there a way to use COUNTIF and COUNTA for when the columns are not right next to each other? I have other data in between each unit column. If not, it's not a big deal; I can create some hidden columns to use for the formula. This was a huge help, thanks!

[DGagnon]

you cannot use those functions on non contiguous ranges, but hiddne helper columns would make it work.

[Cyali]

Excellent, thanks! That's a lot less daunting then a ton of nested IFs.

[Cyali]

Ok, so I had a chance to try that formula out, and it didn't work - any blank cells were seen as mismatches. I think it's because in the empty cells I have formulas (since the unit in the cells is derived from a larger unit - the item/pack size - and if there is no larger unit because the vendor doesn't supply an item, then the cell stays blank). How can I change it around so that it will work?

Edit: Here's an updated example sheet.
Example2.xlsx

[Cyali]

Sorry for the bump, but I still need help with this issue.

[rylo]

Hi

Hows this

Please Login or Register  to view this content.

rylo

[abousetta]

Maybe try this formula:

Please Login or Register  to view this content.

entered with Ctrl + Shift + Enter instead of Enter only as it is an array formula.

It first checks to make sure all the cells from C3:F3 are not empty and then if they are not checks to make sure that they are all the same.

Hope this helps.

[Cyali]

Thank you both, both formulas worked perfectly!