I have an Excel file with 2 sheets. Sheet 1 has a column of part numbers. Sheet 2 also has a column of part numbers; I want to check this column against sheet 1, and if there is a part number on it that isn't on sheet 1, then I want the cell next to that part number to say "Add to sheet1".

I've toyed with IFERROR statements and Indexing, but have had no success so far. How can I accomplish this?


Assuming the parts number are in column A for both sheets, the following formula in Sheet2 should work:

=IF(COUNTIF(Sheet1!A:A,A2)=0,"Add to Sheet1","")

(populate it down for all your part numbers)

| improve this answer | |
  • The “A:A” syntax is a good way to go. – whiskeychief Feb 15 '19 at 10:53

I'll focus on using IFERROR, since that's what you attempted.

If this column is just for an error flag, and you don't care what's displayed as long as you get the message when needed, you can use IFERROR. That returns the formula result as long as there isn't an error, or your message if there's an error. So using it with MATCH, you could do something like this. Assuming your values are in column A of both sheets, B1 of Sheet2 could contain:

=IFERROR(MATCH(A1,Sheet1!$A$1:$A$1000,0),"Add to Sheet1")

Use an appropriate range for Sheet1. This will display the "Add to Sheet1" message when needed, but will also display a number (the MATCH result) in other cases.

If you only want to see either the message or a blank cell, use IF and ISERROR:

=IF(ISERROR(MATCH(A4,Sheet1!$A$1:$A$1000,0)),"Add to Sheet1","")
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.