Home

Help with excel formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-794823%22%20slang%3D%22en-US%22%3EHelp%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794823%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%203%20sheets%20in%20word%20doc%20.%20I%20have%20very%20simple%20one%20conditions%20that%20goes%20in%20main%20sheet%20column%20D8%3C%2FP%3E%3CP%3E1.%20%3DIF(AND(Sheet2!%24E%242%3A%24E%2410000%26lt%3B%3D0.01%2CSheet1!%24E%242%3A%24E%2410000%26gt%3B%3D0.01)%2C1%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIssues%20I%20am%20getting%20is%20%3A%20I%20have%20to%20compare%20Roomids%2C%20Day%20Name%20and%20time%20slot%20to%20put%20correct%20values.%3C%2FP%3E%3CP%3EHow%20to%20match%26nbsp%3BRoomids%2C%20Day%20Name%20and%20time%20slot%20in%20Sheet%201%20and%20Sheet2%20to%20Main%20Sheet%20and%20put%20the%20above%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%20and%20Sheet2%20have%20similar%20pattern.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-794823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794858%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389085%22%20target%3D%22_blank%22%3E%40shilpa288%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20facilitate%20testing%2C%20please%20attach%20your%20sample%20Excel%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794894%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20%3A%20Apologies.%20Please%20find%20sample%20for%20testing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796113%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389085%22%20target%3D%22_blank%22%3E%40shilpa288%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20%20(INDEX(Sheet1!%24E%3A%24E%2CMATCH(1%2CINDEX((Sheet1!%24A%3A%24A%3D%24B8)*(Sheet1!C%3AC%3DD%245)*(Sheet1!%24B%3A%24B%3D%24C8)%2C0)%2C0))%26gt%3B%3D0.01)*%0A%20%20%20%20(INDEX(Sheet2!%24E%3A%24E%2CMATCH(1%2CINDEX((Sheet2!%24A%3A%24A%3D%24B8)*(Sheet2!C%3AC%3DD%245)*(Sheet2!%24B%3A%24B%3D%24C8)%2C0)%2C0))%26lt%3B%3D0.01)%2C%0A%20%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812831%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You.%20It%20worked%20for%20me.%3C%2FP%3E%3CP%3ESorry%20for%20late%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813151%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389085%22%20target%3D%22_blank%22%3E%40shilpa288%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
shilpa288
New Contributor

Hi, 

I have attached 3 sheets in word doc . I have very simple one conditions that goes in main sheet column D8

1. =IF(AND(Sheet2!$E$2:$E$10000<=0.01,Sheet1!$E$2:$E$10000>=0.01),1,0)

 

Issues I am getting is : I have to compare Roomids, Day Name and time slot to put correct values.

How to match Roomids, Day Name and time slot in Sheet 1 and Sheet2 to Main Sheet and put the above formula?

 

Sheet 1 and Sheet2 have similar pattern.

 

 

 

5 Replies
Highlighted

@shilpa288 

To facilitate testing, please attach your sample Excel file.

@Twifoo  : Apologies. Please find sample for testing.

@shilpa288 

Perhaps

=IFERROR(
    (INDEX(Sheet1!$E:$E,MATCH(1,INDEX((Sheet1!$A:$A=$B8)*(Sheet1!C:C=D$5)*(Sheet1!$B:$B=$C8),0),0))>=0.01)*
    (INDEX(Sheet2!$E:$E,MATCH(1,INDEX((Sheet2!$A:$A=$B8)*(Sheet2!C:C=D$5)*(Sheet2!$B:$B=$C8),0),0))<=0.01),
 "no such")

 

@Sergei Baklan 

Thank You. It worked for me.

Sorry for late reply.

@shilpa288 , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies