Need help figuring out 2 formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2960169%22%20slang%3D%22en-US%22%3ENeed%20help%20figuring%20out%202%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2960169%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20lost.%20I%20need%20some%20expert%20help%20figuring%20out%20two%20formulas%20(see%20sample%20data%20attached).%20The%20data%20is%20in%20the%20blue%20and%20yellow%20cells.%20The%20results%20are%20in%20the%20orange%20cells.%26nbsp%3BIf%20someone%20responds%20I%20will%20explain%20what%20I%20need.%3C%2FP%3E%3CP%3EI'm%20on%20a%20PC%20using%20Windows%2010.%26nbsp%3BThx.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2960169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2960222%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20figuring%20out%202%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2960222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216932%22%20target%3D%22_blank%22%3E%40bnatco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24A%245%3A%24A%2412%2CMATCH(1%2C(A18%26gt%3B%3D%24B%245%3A%24B%2412)*(A18%26lt%3B%3D%24C%245%3A%24C%2412)%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24D%245%3A%24F%2412%2CINDEX(%24A%245%3A%24A%2412%2CMATCH(1%2C(A18%26gt%3B%3D%24B%245%3A%24B%2412)*(A18%26lt%3B%3D%24C%245%3A%24C%2412)%2C0))%2CMATCH(B18%2C%24D%244%3A%24F%244%2C0))%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20these%20the%20formulas%20you%20are%20looking%20for%3F%20I%20have%20to%20enter%20these%20formulas%20with%20ctrl%2Bshift%2Benter%20as%20i%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2960303%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20figuring%20out%202%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2960303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216932%22%20target%3D%22_blank%22%3E%40bnatco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(%24A%245%3A%24A%2412%2C(%24B%245%3A%24B%2412%26lt%3B%3DA18)*(%24C%245%3A%24C%2412%26gt%3B%3DA18))%0A%0A%3DINDEX(%24D%245%3A%24F%2412%2CC18%2CMATCH(B18%2C%24D%244%3A%24F%244%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2960459%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20figuring%20out%202%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2960459%22%20slang%3D%22en-US%22%3EDon%E2%80%99t%20know.%20I%E2%80%99ll%20check%20it%20out.%20Thanks%20for%20the%20reply.%3C%2FLINGO-BODY%3E
New Contributor

I'm lost. I need some expert help figuring out two formulas (see sample data attached). The data is in the blue and yellow cells. The results are in the orange cells. If someone responds I will explain what I need.

I'm on a PC using Windows 10. Thx.

3 Replies

@bnatco 

=INDEX($A$5:$A$12,MATCH(1,(A18>=$B$5:$B$12)*(A18<=$C$5:$C$12),0))

 

=INDEX($D$5:$F$12,INDEX($A$5:$A$12,MATCH(1,(A18>=$B$5:$B$12)*(A18<=$C$5:$C$12),0)),MATCH(B18,$D$4:$F$4,0)) 

 

Are these the formulas you are looking for? I have to enter these formulas with ctrl+shift+enter as i don't work with Office365 or 2021.

@bnatco 

=FILTER($A$5:$A$12,($B$5:$B$12<=A18)*($C$5:$C$12>=A18))

=INDEX($D$5:$F$12,C18,MATCH(B18,$D$4:$F$4,0))
Don’t know. I’ll check it out. Thanks for the reply.