Replacement of "IF(OR( ))"

[wilchong via OfficeKB.com]

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value in
cell of A1, B1 and C1 is 2,6 & 2. I place this formular, IF(OR(A1=B1, A1=C1)
, A1, "-") in the cell A5 and the result shown in A5 is 2.

My question is that I want to know is there other excel formular can replace
this formula? The reason I want to know because in the real world, it may
involve more than 3 cells e.g. 5 cells. The multiple effect of the formular
will be extremely massive if I still apply this formular: IF(OR( )).

I hope you can advice me on this. Many thanks,
Wilchong

 

[T. Valko]

You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-") situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1, "-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5 is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong

You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value

[quoted text clipped - 12 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[Roger Govier]

Hi

Try
=IF(COUNTIF(A1:C1,A1)>1,A1,"-")

--
Regards
Roger Govier

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-")
situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1,
"-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can
be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5
is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong

You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value

[quoted text clipped - 12 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[T. Valko]

I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)>1,A1,"-")

Copy across to a total of 3 cells.

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-")
situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1,
"-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can
be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5
is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong

You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value

[quoted text clipped - 12 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Good morning Valko,
Your suggested formula is working perfectly.

Many thank,
Wilchong

I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)>1,A1,"-")

Copy across to a total of 3 cells.

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your

[quoted text clipped - 26 lines]

 

[wilchong via OfficeKB.com]

Hi Roger,
I used your suggested formular and it really work very well.

Many thanks,
Wilchong

Hi

Try
=IF(COUNTIF(A1:C1,A1)>1,A1,"-")

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your

[quoted text clipped - 26 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Good morning Valko,
Your suggested formula is working perfectly.

Many thank,
Wilchong

I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)>1,A1,"-")

Copy across to a total of 3 cells.

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your

[quoted text clipped - 26 lines]

I hope you can advice me on this. Many thanks,
Wilchong