Search code examples
excelexcel-formulainventoryinventory-managementsku

Excel: formula to identify missing sku

I have a list of 1500 sku. Currently we have an active inventory of 450 sku. I am looking for an excel formula that will identify when the sku inputed is not an active sku of the 450.

I have tried various IF functions, but having a hard time breaking it down.

I have tried this:

=IF($B2=F1:F460, COUNTIF(PRODUCTION!$A$1:$AJ$45, $d2), "")

The first part of the equation is where we input the inventory. F1:f460 is a list of all the active sku. The second part of the code is where it goes to look for the code if it on the production sheet.

I need to know when we put a sku into the inventory if it is correct or not.

Solution 
  • =SUMPRODUCT(--(SkuToTest=F1:F460))>0

    The logic behind this is explained here. If you want to show only skus that are not in your list, you can wrap it in an IF like so:

    =IF(SUMPRODUCT(--(SkuToTest=F1:F460))>0,"","FALSE")