Cell Reference Lookup help

MitchellG: Not all that sure what you're after, but try this:
in DT Dept 20 tab G1 type in the word HELPER
In DT Dept 20 tab G2 ENTER: =B2&C2&D2 and copy down.

In Dept 20 (3099) tab in C13 type =VLOOKUP(13099&D13,CHOOSE({1,2},'DT Dept. 20'!G:G,'DT Dept. 20'!F:F),2,FALSE) and copy down to cover both shifts if you want.

I've assumed you want the corresponding AC figure from the DT tab and brought in next to the AC labeled cell as in D13. If you wanted it in C12 per your Original post, change the formula from the D13 reference to AC. Hence was my confusion.

I also edited your source data on the DT tab to give your original criteria something to find. You need a 3099,a 1, and an AC. Be sure it's there.

Hope this gets you started.

Pete

I think you can use SUMIFS
In C12 copied down

=SUMIFS('DT Dept. 20'!$F$2:$F$61, 'DT Dept. 20'!$D$2:$D$61, D12,'DT Dept. 20'!$B$2:$B$61, 1)

In C26 copied down

=SUMIFS('DT Dept. 20'!$F$2:$F$61, 'DT Dept. 20'!$D$2:$D$61, D26,'DT Dept. 20'!$B$2:$B$61, 2)

I'd use ChemistB's SUMIFS example. Format as general.
Paste into C54: =SUMIFS('Scrap Dept. 20'!D:D,'Scrap Dept. 20'!F:F,E54,'Scrap Dept. 20'!A:A, 1,'Scrap Dept. 20'!E:E, D54)
Paste into C64: =SUMIFS('Scrap Dept. 20'!D:D,'Scrap Dept. 20'!F:F,E64,'Scrap Dept. 20'!A:A, 2,'Scrap Dept. 20'!E:E, D64)

Copy both formulas down, and report back.

Pete