Put the list of out of stock products in a column away from your main list.
For this example I had product data in column A and the list of out of stock products in column I (cells I1 to I14)
In an empty column, say column D, enter this formula on the same row as the first product:
=IF(ISNA(VLOOKUP(A1,$I$1:$I$14,1,FALSE)),"","Not in stock")In this example, my sample data started in row 1 - adjust as appropriate.
Drag the formula down as many rows as you have products in column A.
Note the $ signs in the formula - these are required to make the formula work when dragged, to extend it.
Now column D will be blank for in stock items, and will show "Not in stock" for items that are present in your list in column I
You can make the formula cover a larger range in column I so that additional out of stock items can be added without having to change the formula.
Blanks in column I will not affect the function, so
=IF(ISNA(VLOOKUP(A1,$I$1:$I$100,1,FALSE)),"","Not in stock")will work just as well.