CODEWITHSUNDEEP
excelexcel-2016
WhatsHisFace
WhatsHisFace

Reputation: 1

How can I compare 2 columns in excel?

I have 2 columns in excel, I need to search each of the values in the first column in the second one and have each value that exists in both them written/marked.

How do I even start this?

Upvotes: 0

Views: 48

Answers (1)

Dominique
Dominique

Reputation: 17491

You might go for conditional formatting, based on this formula:

=NOT(ISERROR(MATCH(A2,B$2:B$7,0)))

=MATCH(A2,B$2:B$7,0) // is also fine
  • Match() searches for a matching result. If not found, an error is shown.
  • Beware: you check for cell "A2", then "A3", then "A4", ..., but the range where you search ("B$2:B$7") does not change, hence the dollarsigns).
  • NOT(ISERROR(...)) basically gives TRUE in case of found, and FALSE in case not. This will be the basis of the conditional formatting.

Edit:
According to Mayukh and verified afterwards, the NOT(ISERROR(...)) or ISNUMBER() construction is not even needed: you can just use =MATCH(A2,B$2:B$7,0) as a formula for your conditional formatting.

Result:

enter image description here

Upvotes: 3

Related Questions