How to use Query function to list products in stock?-CodePudding

In my query function (cell H3), I want to list products which are acutally in stock.

If Salesperson Amy's note says "Sold" then this product would be considerd as Sold. (Even if Salesperson jack says "Reserved")

And I also want to remove all duplicates. Thank you so much for your help.

CodePudding user response：

use:

=INDEX(UNIQUE(QUERY({B2:D, C2:C&D2:D}, "select Col1 where Col4 contains 'Sold'")))

update:

=UNIQUE(FILTER(B2:B, NOT(REGEXMATCH(B2:B, 
 TEXTJOIN("|", 1, INDEX(UNIQUE(QUERY({B2:D, C2:C&D2:D}, 
 "select Col1 where Col4 contains 'Sold'"))))))))