I am trying to compare the values in B4 down to B57 with the value in
E4. If any of the values in the B column match the value in E4 then 1
is put in K4. The only trouble is i am using the IF function and it
appears that it will only accept 6 IF's in any one formula, so i am
having to write the formulas as below:
=IF(E4=$B$4,1,IF(E4=$B$5,1,IF(E4=$B$6,1,IF(E4=$B$7 ,1,IF(E4=$B$8,1,IF(E4=$B$9,1,0))))))
thru to:
=IF(E4=$B$52,1,IF(E4=$B$53,1,IF(E4=$A$54,1,IF(E4=$ A$55,1,IF(E4=$A$56,1,IF(E4=$A$57,1,0))))))
value placed in S4.
The total number of 1's in K4 to S4 are added and the result placed in
BD.
The problem is that if any of the values in B4 to B9 are duplicated
only one is accounted for in BD, but if the value in say B9 and B10 is
the same both values are accounted for and BD is 2.
Does anyone know how to overcome this problem as i dont want to count
the duplicate values.


--
Reefaman
------------------------------------------------------------------------
Reefaman's Profile: http://www.excelforum.com/member.php...o&userid=31968
View this thread: http://www.excelforum.com/showthread...hreadid=516947

In K4 put....

=IF(ISNA(VLOOKUP(E4,B4:B57,1,FALSE)),"",1)

Vaya con Dios,
Chuck, CABGx3



"Reefaman" wrote:


I am trying to compare the values in B4 down to B57 with the value in
E4. If any of the values in the B column match the value in E4 then 1
is put in K4. The only trouble is i am using the IF function and it
appears that it will only accept 6 IF's in any one formula, so i am
having to write the formulas as below:
=IF(E4=$B$4,1,IF(E4=$B$5,1,IF(E4=$B$6,1,IF(E4=$B$7 ,1,IF(E4=$B$8,1,IF(E4=$B$9,1,0))))))
thru to:
=IF(E4=$B$52,1,IF(E4=$B$53,1,IF(E4=$A$54,1,IF(E4=$ A$55,1,IF(E4=$A$56,1,IF(E4=$A$57,1,0))))))
value placed in S4.
The total number of 1's in K4 to S4 are added and the result placed in
BD.
The problem is that if any of the values in B4 to B9 are duplicated
only one is accounted for in BD, but if the value in say B9 and B10 is
the same both values are accounted for and BD is 2.
Does anyone know how to overcome this problem as i dont want to count
the duplicate values.


--
Reefaman
------------------------------------------------------------------------
Reefaman's Profile: http://www.excelforum.com/member.php...o&userid=31968
View this thread: http://www.excelforum.com/showthread...hreadid=516947

The way your formula begins, you're not counting the matches, but only
determining if there is any match (once one IF finds a match, you're setting
the result to 1). If you want the count, try =countif(b4:b57,e4). If you
just want to see if there is a match, =if(countif(b4:b57,e4)0,1,0).
--Bruce

"Reefaman" wrote:


I am trying to compare the values in B4 down to B57 with the value in
E4. If any of the values in the B column match the value in E4 then 1
is put in K4. The only trouble is i am using the IF function and it
appears that it will only accept 6 IF's in any one formula, so i am
having to write the formulas as below:
=IF(E4=$B$4,1,IF(E4=$B$5,1,IF(E4=$B$6,1,IF(E4=$B$7 ,1,IF(E4=$B$8,1,IF(E4=$B$9,1,0))))))
thru to:
=IF(E4=$B$52,1,IF(E4=$B$53,1,IF(E4=$A$54,1,IF(E4=$ A$55,1,IF(E4=$A$56,1,IF(E4=$A$57,1,0))))))
value placed in S4.
The total number of 1's in K4 to S4 are added and the result placed in
BD.
The problem is that if any of the values in B4 to B9 are duplicated
only one is accounted for in BD, but if the value in say B9 and B10 is
the same both values are accounted for and BD is 2.
Does anyone know how to overcome this problem as i dont want to count
the duplicate values.


--
Reefaman
------------------------------------------------------------------------
Reefaman's Profile: http://www.excelforum.com/member.php...o&userid=31968
View this thread: http://www.excelforum.com/showthread...hreadid=516947

Thanks for the replies. Tried Chucks version and it works just as i
wanted.
Cheers


--
Reefaman
------------------------------------------------------------------------
Reefaman's Profile: http://www.excelforum.com/member.php...o&userid=31968
View this thread: http://www.excelforum.com/showthread...hreadid=516947