I'm using the formula below in column A of Book2.xlsm to try to find duplicate addresses in the file Book1.xlsx. I'd like the cells in Book2.xlsm column A to be blank if the respective cell in column F is blank, and if the respective cell in column F is not blank, column A should show "Duplicate" if an exact match exists in column D of Book1.xlsx OR "New" if an exact match does not exist in column D of Book1.xlsx.



=IF(ISBLANK(F2),"",IFERROR(IF(MATCH(F2,'C:\Temp\PO TS\[Book1.xlsx]ALL'!$D$2:$D$2001)>0,"Duplicate"),"New"))

I've gotten the basics right as far as I can tell, because I am getting "New" or "Duplicate" in column A - but not in the correct locations. I've tried changing the format of the columns to different types, but still no luck so far. Any help greatly appreciated.

I'm also open to other methods to do this such as vba/macro or something not formula based (like Conditional Formatting that would highlight the row of the duplicates), this is just what I came up with.

Attached Files

	Book1.xlsx (305.9 KB, 10 views)
	Book2.xlsm (60.3 KB, 10 views)

Try:
=IF(F2="","",IF(COUNTIF([Book1.xlsx]ALL!$D:$D,F:F)=0,"New","Duplicate"))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks macropod - that worked great! I'm so familiar with the ISBLANK and ISERROR functions I never looked into using COUNTIFS. Great solution!