Not an easy one!

Why not ask your supplier to provide a list of discontinued items? Surely, as they are removing them from the list, what they could do is add a column to the spreadsheet with the header DISCONTINUED, and if the product is discontinued, their admin clerk puts a X in the cell alongside the appropriate product.

Contact them to explain the situation.

One of my clients works with a very reputable UK dropshipper, and on their site they have a daily notifications board. Here they list:

1. New items spreadsheet (EP v_status = 1)
2. Out of Stock spreadsheet (EP v_status = 0)
3. Back in Stock spreadsheet (EP v_status = 1)
4. Discontinued products spreadsheet (EP v_status = 9)
5. Full current product list.

Running these through EP takes a few minutes. All dropshippers/suppliers ought to have this sort of facility.

----------------------

You may have some success using XLOOKUP or LOOKUP features in MS Excel, or by doing a pivot table.