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