SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2302448%22%20slang%3D%22en-US%22%3EEXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20stuck%20with%20a%20formula.%20I%20have%202%20data%20sets%20with%20date%20ranges.%20I%20am%20trying%20IF%20statements%20and%20INDEX%20MATCH%20statements%20and%20none%20seem%20to%20be%20returning%20the%20data%20I'm%20looking%20for.%20The%20first%20sheet%20for%20example%20has%2C%20apples%20sold%3A%2030%20units%20between%201%2F01%2F2021%20-%201%2F07%2F2021%2015%20units%20between%201%2F08%2F2021%20-%201%2F14%2F2021%2010%20units%20between%201%2F15%2F2021%20-%201%2F21%2F2021%20The%20second%20sheet%20shows%20apples%20was%20on%20promotion%20%23123%2C%20between%201%2F05%2F2021%20-1%2F10%2F2021.%20I%20need%20the%20promotion%20%23123%20to%20return%20its%20value%20in%20the%20first%20sheet.%20Can%20you%20please%20help%20me%3F%20I%20don't%20even%20know%20which%20formula%20I%20need%20to%20google%20to%20figure%20this%20out%20for%20myself.%20Thank%20you!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2302448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2302966%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038929%22%20target%3D%22_blank%22%3E%40Dble_elle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20F2%20on%20Sheet1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Sheet2!%24C%242%3A%24C%243%2CMATCH(1%2CINDEX((Sheet2!%24D%242%3A%24D%243%26lt%3B%3DE2)*(Sheet2!%24E%242%3A%24E%243%26gt%3B%3DD2)%2C%2C)%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2303227%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2303227%22%20slang%3D%22en-US%22%3EYou're%20amazing!%20Thank%20you!!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304250%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304250%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20--%20I%20have%20updated%20my%20EXAMPLE%20on%20the%20attachment.%20It%20is%20now%20more%20%22complicated%22.%20Can%20you%20help%20me%20figure%20out%20what%20I%20need%20to%20change%20in%20the%20formula%20in%20order%20to%20account%20for%20multiple%20item%20numbers%20that%20have%20different%20promotion%20numbers%20but%20during%20the%20same%20promotion%20dates%3F%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304291%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038929%22%20target%3D%22_blank%22%3E%40Dble_elle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20would%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Sheet2!%24C%242%3A%24C%247%2CMATCH(1%2CINDEX((Sheet2!%24A%242%3A%24A%247%3DA2)*(Sheet2!%24D%242%3A%24D%247%26lt%3B%3DE2)*(Sheet2!%24E%242%3A%24E%247%26gt%3B%3DSheet1!D2)%2C%2C)%2C0))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304453%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304453%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20--%20That%20worked!%20Thank%20you%20so%20much!!%20YOU%20are%20by%20FAR%20the%20SMARTEST%20PERSON%20i've%20%22met%22.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304460%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FORMULA%20HELP%20-%20LOOKUP%20VALUE%20BETWEEN%202%20DATASETS%20CONDITIONAL%20ON%20DATE%20RANGES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038929%22%20target%3D%22_blank%22%3E%40Dble_elle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20%3CBLUSH%3E%3C%2FBLUSH%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@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.

@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

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

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