Multiple Lookups for the same Item Number (VLookup, Index, Match, etc.)

[CChinn]

Hello,
I'm looking for a formula that will achieve the following.
Any help would be greatly appreciated.

Sheet1

Sheet2

On Sheet2, I would put the formula in C2, and copy throughout the sheet.
If cells B&A (280002443601) on Sheet2 is listed in column "X" on Sheet1 and the date in cell C1 (1/22/2024) on Sheet2 is >= to the date in column AB on Sheet1 and <= to date in column AC on Sheet 1, then return the value showing in column AE on Sheet1 for that row.

In the data listed above, I would want the below information to show.
For row 3 (3602), it would return 1,427 from 1/22/24 - 2/24/24.
For row 4 (3603), it would return 1,427 from 1/22/24 - 2/26/24.

Is this possible?
If not, and you know a better option to pull that data, please let me know.

Thanks!

 

[DanteAmor]

Try this in C2 on Sheet2:

=SUMPRODUCT((Sheet1!$X$15:$X$2000=$B2&$A2)*(C$1>=INT(Sheet1!$AB$15:$AB$2000))*(C$1<=INT(Sheet1!$AC$15:$AC$2000))*(Sheet1!$AE$15:$AE$2000))