EXCEL FORMULA HELP - LOOKUP VALUE BETWEEN 2 DATASETS CONDITIONAL ON DATE RANGES

[Dble_elle]

I am stuck with a formula. I have 2 data sets with date ranges. I am trying IF statements and INDEX MATCH statements and none seem to be returning the data I'm looking for. The first sheet for example has, apples sold: 30 units between 1/01/2021 - 1/07/2021 15 units between 1/08/2021 - 1/14/2021 10 units between 1/15/2021 - 1/21/2021 The second sheet shows apples was on promotion #123, between 1/05/2021 -1/10/2021. I need the promotion #123 to return its value in the first sheet. Can you please help me? I don't even know which formula I need to google to figure this out for myself. Thank you!

[#2]

@Dble_elle 

In F2 on Sheet1:

=IFERROR(INDEX(Sheet2!$C$2:$C$3,MATCH(1,INDEX((Sheet2!$D$2:$D$3<=E2)*(Sheet2!$E$2:$E$3>=D2),,),0)),"")

Fill down.

[#3]

You're amazing! Thank you!!

[#4]

@Hans Vogelaar -- I have updated my EXAMPLE on the attachment. It is now more "complicated". Can you help me figure out what I need to change in the formula in order to account for multiple item numbers that have different promotion numbers but during the same promotion dates? Thank you.

[#5 · Accepted solution]

@Dble_elle 

That would be

=IFERROR(INDEX(Sheet2!$C$2:$C$7,MATCH(1,INDEX((Sheet2!$A$2:$A$7=A2)*(Sheet2!$D$2:$D$7<=E2)*(Sheet2!$E$2:$E$7>=Sheet1!D2),,),0)),"")

[#6]

@Hans Vogelaar -- That worked! Thank you so much!! YOU are by FAR the SMARTEST PERSON i've "met". :)

[#7]

@Dble_elle 

Thank you <blush>