A few words of thanks would be greatly appreciated.

Excel - Comparing one column data with multiple column


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.


Try this formula:
=if(countif(B:D,"=" & A1)>0,A1,"") 

in cell A5 and drag it down


Thanks to Excelguru for this tip on the forum.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Published by . Latest update on by aakai1056.

This document, titled "Excel - Comparing one column data with multiple column," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).