Comparing one column data with multiple colum

Closed
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello all,

I have four columns. Each column has data ranging from 1 - 50 rows. These four column has common data like below:

A B C D

1 4 3 100
2 10 4 55
3 20 2 5
4 2 50 3
5 18 70 50
6 25 45 7
7 30 8 2
8 12 10 6
.
.
.

1. I want to have the data output in such a way that same data found in any 3 columns out of 4 columns taking column A as reference (i.e. Column A data needs to be checked across all the other 3 columns for similar data).

2. I want to get the similar data for the first 2 column keeping column A as reference (i.e Checking all the data of column A in B)

The output of first question to be marked in blue and output of second question marked in Red.

Hope you have understood the question. Advance thanks for your help.

Regards,
SDN

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Try =if(countif(B:D,"=" & A1)>0,A1,"") in cell A5 and drag it down