Multiple Criteria Lookup with Date Ranges

[neg09]

Hi everyone,

I need to create a lookup which returns a value based on 1.) an employee ID 2.) a date range.

I have included an example image. Basically, I need a formula for column H which returns the correct Union of an employee, based on their ID and the award date (column G). The dataset I'm looking up from will have multiple rows of data per employee, and potentially a different Union value, depending on the date.

I have figured out a way to return a value based on date ranges, but can't figure out how to incorporate the employee ID into the mix. Any help would be much appreciated!!

 

[Bo_Ry]

Please try at H2
=LOOKUP(1,1/($A$2:$A$5=F2)/($C$2:$C$5<=G2)/($D$2:$D$5>=G2),$B$2:$B$5)

 

[neg09]

Thank you! That works. I understand most of the formula, but could you explain what the '1,1' means?

Thanks again.

 

[Bo_Ry]

1/( True) = 1
1/(false) =1/0 =#div/0!
lookup(1 , look for 1 and ignore div/0

 

[neg09]

Thank you!

 

[joe_pilgrim]

Hello Excel Community,

I am piggybacking on this thread as it is the closest to what I am seeking assistance with. I am setting up my quarterly inventory and while I using an IF statement to perform the test and combining it with the LOOKUP function it seems to do the job. However, I do not believe the logic/function I am using is correct, hence I need your assistance. Below is the formula I am using:

=IF(AND($H6>=P$2,$H6<=P$3),(LOOKUP(2,1/(DATASETS!$F$4:$F$55<=WIDGET_INVENTORY!$H6)/(DATASETS!$G$4:$G$55>=WIDGET_INVENTORY!$H6),DATASETS!$H$4:$H$55))*(40),((40*P$1))

H6 = Expiration Date
P2 = January's start date, 12.29.2019
P3 = January's end date, 01.26.2020

DATASETS worksheet containing 52-weeks or 4-4-5 quarter weeks
F4:F55 = start date for every week, starting at F4 with 12.29.2019
G4:G55 = end date for every week, starting at G4 with 01.05.2020

The formula above seems to be working when testing against January's start and end date, P2 and P3 respectively.

The formula is not properly working when testing against February's start and end dates, S2 (01.27.2020) and S3 (02.23.2020) respectively. The formula is not properly working when testing against March's start and end dates, V2 (02.24.2020) and V3 (03.29.2020) respectively.

For example, I have four widgets with four different expiration dates:

H6 = 12.31.2020
H7 = 03.31.2020
H8 = 12.31.2020
H9 = 01.17.2020
H10 = 02.14.2020

When I copy/paste the formula on R6 to test against February's start and end dates, the results for 12.31.2020 expiration date works fine. The results on R7 for 03.31.2020 expiration date works fine. The results on R8 for 01.17.2020 expiration date should result in zero but is returning the FALSE part of the formula. The results on R9 for 02.14.2020 expiration date works fine.

When I copy/paste the formula on U6 to test against March's start and end dates, the results for 12.31.2020 expiration date works fine. The results on U7 for 03.31.2020 expiration date works fine. The results on U8 for 01.17.2020 expiration date should result in zero but is returning the FALSE part of the formula. The results on U9 for 02.14.2020 expiration date should result in zero but is returning the FALSE part of the formula.

Below is a screenshot of what the results should be for FEBruary and MARch.

Any assistance is greatly appreciated. Let me know if there any questions, thanks in advance.


JP

 

[joe_pilgrim]

Please disregard as I solved my own question. Thank you all for lurking.


JP