Sum if Multiple criteria matches across 2 sheets

Berbay

Hi,

I have two sheets containing a table of items. Sheet 1 is for entire item list and sheet 2 is for items used in a day. I want the sheet 1 to check the sheet 2 and reduce the numbers in the sheet 1 automatically. The problem I need to match two criterias. I tried following after making it work for one criteria. Any help would be highly appreciated.

=IFERROR(Qty@row - INDEX({Day Range 4}, ({QR Read Range 6}), (MATCH([Lot No.]@row, {Day Range 6}, 0)), IF({QR Read Range 6} >=[Cat. No.]@row,1))) "No match"))

=IFERROR(Qty@row - INDEX(COLLECT({Day Range 4}, (MATCH([Cat. No.]@row, {Day Range 6}, 0)), MATCH([Lot No.]@row, {Day Range 3}, 0), 1), "No match"))  

Paul Newcome

You don't use the MATCH function when you are using the COLLECT function. Give this a try:

=IFERROR(Qty@row - INDEX(COLLECT({Day Range 4}, {Day Range 6}, [Cat. No.]@row, {Day Range 3}, [Lot No.]@row), 1), "No match")