Home Ask Login Register

Developers Planet

Your answer is one click away!

Gage Brklacich February 2016

Sorting and finding duplicates in excel columns

I have five columns in my spreadsheet, three of which are filled with assorted names( the first, fourth and fifth columns).

Excel spreadsheet

I need a way to cross-reference each cell in the A column with the D and E columns, then have an output that answers the question in the B and C column (which you can see as the Xs), as to whether it was found. I've tried a combination of VLOOKUP and MATCH, but this is proving to be out of my realm. I haven't used excel much lately.

EDIT: Added a picture instead of a diagram


chris neilsen February 2016

In Cell B3 use =IF(COUNTIFS(D:D,A:A),"","X")

and in C3 use =IF(COUNTIFS(D:D,A:A),"","X")

copy down as far as required

Formula says "If count of names in D:D equal to name in current row in A:A is > 0 then return blank, else return "X"

Test is case-insensitive.

Post Status

Asked in February 2016
Viewed 3,990 times
Voted 7
Answered 1 times


Leave an answer

Quote of the day: live life