sunnyrainy
New Member
- Joined
- Apr 13, 2022
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
Hi, I have been searching a solution here but can't seem to find a fit. Much much grateful if you can help figure out the formula/macro/other automation:
Scenario 1
Sheet 1: Same ID can have different rows of Start Date/End Date and Location Code
Sheet 2: Would like to lookup the Location Code from sheet 1 based on the Product ID AND Sell Date that falls within the Start Date-End Date
Example:
-Product ID: 123456-1 with Sell Date: 17-03-2017
-Fall into Sheet 1 date B1-C3
-Return Location Code as sheet 1 D2
Scenario 2: Added an extra condition
Sheet 1: Same ID can have different rows of Start Date/End Date/ Location Code and Quality Check Status
Sheet 2: Would like to lookup the Location Code from sheet 1 based on the Product ID AND Sell Date that falls within the Start Date-End Date AND Quality Check as "Y"
Example:
-Product ID: 123456-1 with Sell Date: 17-03-2017
-Fall into Sheet 1 both dates B1-C3 / C3-D3
-Fulfill condition: Quality Check "Y" i.e. B2
-Return Location Code as sheet 1 E2 instead of E3
Thank you!
Scenario 1
Sheet 1: Same ID can have different rows of Start Date/End Date and Location Code
Sheet 2: Would like to lookup the Location Code from sheet 1 based on the Product ID AND Sell Date that falls within the Start Date-End Date
Example:
-Product ID: 123456-1 with Sell Date: 17-03-2017
-Fall into Sheet 1 date B1-C3
-Return Location Code as sheet 1 D2
Scenario 2: Added an extra condition
Sheet 1: Same ID can have different rows of Start Date/End Date/ Location Code and Quality Check Status
Sheet 2: Would like to lookup the Location Code from sheet 1 based on the Product ID AND Sell Date that falls within the Start Date-End Date AND Quality Check as "Y"
Example:
-Product ID: 123456-1 with Sell Date: 17-03-2017
-Fall into Sheet 1 both dates B1-C3 / C3-D3
-Fulfill condition: Quality Check "Y" i.e. B2
-Return Location Code as sheet 1 E2 instead of E3
Thank you!