Finding Data in an Array

[Guest]

Hi,

I have four columns of data and these cells will all have unique entrys from
A1 to L20. However, this information may change round so that some times not
all the possible entries will be used. From this information then, I'd like
to create an IF statement that identifies whether (for example) A1 is present
in any of the four cells. If it is I'd like the cell to show T but if not I'd
like the cell to show A.

I'd be greatful if anyone could point me in the right direction as I'm sure
there's an answer somewhere!

Hannah

 

[Guest]

=IF(OR(A1=Cell1,A1=Cell2,A1=Cell3,A1=Cell4),"T","A")

Not sure about the 4 cell addresses but just change cell1 etc to B1 etc.

I assume show A meant literal "A"

HTH

 

[Guest]

Thanx, Topper. The only problem is that the columns go from 1-1000. Any ideas?

 

[Guest]

Is this what is required?

=IF(SUMPRODUCT(--($A$1:$A$10=E1))+SUMPRODUCT(--($B$1:$B$10=E1))+SUMPRODUCT(--($C$1:$C$10=E1))+SUMPRODUCT(--($D$1:$D$10=E1)),"T","A")

Or


=IF(SUMPRODUCT(--($A$1:$D$10=E1)),"T","A")

This will return "T" if E1 is found in any of columns A to D

 

[Guest]

...or just copy the formula down for the required rows.

Enter this in first cell and then copy down: B1 will become B2 etc.

=IF(OR(B1="A1",C1="A1",D1="A1",E1="A1"),"T","A")

OR

=IF(OR(B1=X1,C1=X1,D1=X1,E1=X1),"T","A")

where X1="A1" or any search value

Ignore posting on SUMPRODUCT!

 

[Guest]

=IF(OR(B1=X1,C1=X1,D1=X1,E1=X1),"T","A")

where X1 contains value to be compared.

Put this in first cell(row) and copy down: B1 will become B2 etc.

Ignore SUMPRODUCT posting!

HTH