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

CChinn

New Member
Joined
Jan 23, 2024
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
I'm looking for a formula that will achieve the following.
Any help would be greatly appreciated.

Sheet1
1706044657289.png


Sheet2
1706044769972.png


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.

1706045335765.png



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

Thanks!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try this in C2 on Sheet2:


Excel Formula:
=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))
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top