Return Criteria If all Cells Match

[ExcelRoy]

Hi,

I am looking for a formula to look through P25:P70 and make sure they are all the same alpha numeric entry

ie
If all entries are ABC123 then return ABC123 if there is one or more anomalies then return "mismatch"

Many thanks for any help

 

[Joe4]

Here is one way:

=IF(COUNTIF(P25:P70,P25)=46,P25,"mismatch")

 

[Fluff]

Beaten 2 it

 

[ExcelRoy]

That's Great, Thanks Joe4

 

[ExcelRoy]

Aplogies herebut could I raise another issue I am having with alpha numeric values

=IFERROR(INDEX(Maintenance!$D$9:$D$2000,MATCH(C27,Maintenance!$C$9:$C$2000,0),0),"")

this returns perfectly well any alpha numeric values but wont return just numbers?

Any ideas why this is?

Thanks

 

[Joe4]

Aplogies herebut could I raise another issue I am having with alpha numeric values

=IFERROR(INDEX(Maintenance!$D$9:$D$2000,MATCH(C27,Maintenance!$C$9:$C$2000,0),0),"")

this returns perfectly well any alpha numeric values but wont return just numbers?

That is a totally different unrelated question, and therefore should be posted in its own new thread.

 

[ExcelRoy]

Hi Joe4,

Sorry and my apologies

Just one thing I have found with your solution to my original question

if there are any blanks at the end of the range it will show the mismatch return, can the formula ignore blanks?

Thanks again

 

[Joe4]

Try this:

=IF(COUNTIF(P25:P70,P25)=46-COUNTBLANK(P25:P70),P25,"mismatch")

 

[ExcelRoy]

Works perfect, many thanks Joe4

(Sorry for the late reply)