CODEWITHSUNDEEP
functionexcelexcel-formulavba
Dan B. Lee
Dan B. Lee

Reputation: 109

Excel - Compare one column to another and display results

In Excel 2007, I have two columns, similar data (list of names). If the name is found in both columns I'd like to change a blank column to active. How can this be done?

Longer Description of what I'd like to do:

Column A:A on Sheet 1 has 300 names. Column A:A on Sheet 2 has 20 names. If the names on Sheet 2 are also in Sheet 1 I'd like to fill Column A:B on Sheet 1 with the word "Active"

How can this be done? Thanks in advance!

Upvotes: 1

Views: 13229

Answers (2)

John Bustos
John Bustos

Reputation: 19554

Assuming you had the names in Column A of both sheets, In cell B1 of Sheet1, put in the following formula:

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Active","Not Active")

You can then drag that formula down - If the name exists in BOTH, it will say "Active" otherwise it will say "Not Active".

Hope this helps.

Upvotes: 4

Stewbob
Stewbob

Reputation: 16899

This can be handled with a simple VLOOKUP formula:

=IF(VLOOKUP(Sheet2!A1,Sheet1!A:A,1,FALSE)=A1,"Active","")

The above formula is placed in cell B1 on Sheet1 and then copied down as far as there is data in column A on sheet 1.

The only caveat with this formula is that the data in Sheet2 column A must be sorted alphabetically.

The FALSE near the end of the formula indicates that an exact match is required.

Upvotes: 2

Related Questions