If Formulas

[Reefaman]

I am trying to compare the values in B4 down to B57 with the value i
E4. If any of the values in the B column match the value in E4 then
is put in K4. The only trouble is i am using the IF function and i
appears that it will only accept 6 IF's in any one formula, so i a
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 i
BD.
The problem is that if any of the values in B4 to B9 are duplicate
only one is accounted for in BD, but if the value in say B9 and B10 i
the same both values are accounted for and BD is 2.
Does anyone know how to overcome this problem as i dont want to coun
the duplicate values

 

[CLR]

In K4 put....

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

Vaya con Dios,
Chuck, CABGx3

 

[bpeltzer]

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]

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