SOLVED

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

Occasional Contributor

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

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!

6 Replies

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

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.

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

You're amazing! Thank you!!

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

@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.
best response confirmed by Dble_elle (Occasional Contributor)
Solution

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

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)),"")

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

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

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

Thank you <blush>