Multiple criteria lookup using date range

Hello,

I am trying to create a lookup using multiple criteria that compares a date to date ranges to pull back a result. I was looking at index and match but this is beyond my skill level and hoped someone could help.

Basically I am trying to say return the year in the lookup table when the ID matches and the date is greater than equal to the begin and less than or equal to the end date.

Criteria Expected Result |Lookup Table
ID Date Year |ID Begin Date End Date Year
CY 1/2/2020 2020 |CY 1/1/2020 12/31/2020 2020
CY 5/2/2021 2021 |CY 1/1/2021 12/31/2021 2021
VA 7/24/2020 2021 |VA 5/1/2019 4/30/2020 2020
VA 9/13/2019 2020 |VA 5/1/2020 4/30/2021 2021
YR 5/24/2020 2020 |VA 5/1/2021 4/30/2022 2022
YR 10/23/2021 2022 |YR 7/1/2019 6/30/2020 2020
|YR 7/1/2020 6/30/2021 2021
|YR 7/1/2021 6/30/2022 2022


I will also try and upload the sheet to make it easier.

Thanks for the help

Stephen

You can use this array* formula in cell C3:

=INDEX($I$3:$I$10,MATCH(1,($F$3:$F$10=A3)*(B3>=$G$3:$G$10)*(B3<=$H$3:$H$10),0))

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Copy down as required.

Hope this helps.

Pete

In C3: =SUMIFS(I$3:I$10,F$3:F$10,A3,G$3:G$10,"<="&B3,H$3:H$10,">="&B3)

Another alternative,

Formula:

Please Login or Register  to view this content.

Wow, a few different ways. Thank you for helping me out.