Apr 28 2021 08:00 AM - edited Apr 28 2021 01:21 PM
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!
Apr 28 2021 09:22 AM
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.
Apr 28 2021 09:52 AM
Apr 28 2021 01:24 PM
Apr 28 2021 01:38 PM
SolutionThat 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)),"")
Apr 28 2021 02:35 PM
Apr 28 2021 02:42 PM
Thank you <blush>
Apr 28 2021 01:38 PM
SolutionThat 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)),"")