Home

IF statement on MS Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-418841%22%20slang%3D%22en-US%22%3EIF%20statement%20on%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418841%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20some%20help%20with%20the%20IF%20formula%20on%20the%20attached%20sheet%3B%20I'm%20not%20clear%20if%20the%20IF%20OR%20statement%20or%20IF%20XOR%20would%20do%20this.%20Column%20C%20has%203%20possible%20entries%2C%20column%20D%20has%205%20possible%20entries%20(on%20a%20drop-down%20list).%20Column%20E%20will%20need%20a%20formula%2C%20to%20compare%20the%20values%20on%20columns%20C%20and%20D%2C%20and%20return%20a%20specific%20value%2C%20for%20the%20respective%20cells%20on%20column%20E%2C%20depending%20on%20the%20selections%20of%20column%20C%20and%20D.%20The%20entries%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20column%20C%2C%20marked%20%22Grade%22%20%3D%206%2C%20and%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Lagos%22%20or%20%22Portharcourt%22%20or%20%22Abuja%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%2212500%22%3B%3C%2FP%3E%3CP%3Eif%20column%20C%2C%20marked%20%22Grade%22%20%3D%205%26amp%3B4%2C%26nbsp%3Band%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Lagos%22%20or%20%22Portharcourt%22%20or%20%22Abuja%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%2213500%22%3B%3C%2FP%3E%3CP%3Eif%26nbsp%3Bcolumn%20C%2C%20marked%20%22Grade%22%20%3D%203%2C%26nbsp%3Band%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Lagos%22%20or%20%22Portharcourt%22%20or%20%22Abuja%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%2218000%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20column%20C%2C%20marked%20%22Grade%22%20%3D%206%2C%20and%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Kano%22%20or%20%22Other%20States%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%228500%22%3B%3C%2FP%3E%3CP%3Eif%20column%20C%2C%20marked%20%22Grade%22%20%3D%205%26amp%3B4%2C%26nbsp%3Band%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Kano%22%20or%20%22Other%20States%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%229500%22%3B%3C%2FP%3E%3CP%3Eif%26nbsp%3Bcolumn%20C%2C%20marked%20%22Grade%22%20%3D%203%2C%26nbsp%3Band%20column%20D%2C%20marked%20%22Location%22%20%3D%20%22Kano%22%20or%20%22Other%20States%22%2C%20then%2C%20column%20E%20should%20return%20the%20value%20%2212000%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-418841%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-419019%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20statement%20on%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317288%22%20target%3D%22_blank%22%3E%40Alfredokosun%3C%2FA%3E%26nbsp%3B%2C%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(IF((D4%3D%22Lagos%22)%2B(D4%3D%22Portharcourt%22)%2B(D4%3D%22Abuja%22)%2CLOOKUP(C4%2C%7B3%2C%225%26amp%3B4%22%2C6%7D%2C%7B18000%2C13500%2C12500%7D)%2CIF((D4%3D%22Kano%22)%2B(D4%3D%22Other%20States%22)%2CLOOKUP(C4%2C%7B3%2C%225%26amp%3B4%22%2C6%7D%2C%7B12000%2C9500%2C8500%7D)%2C%22%22))%2C%22wrong%20data%22)%3C%2FPRE%3E%0A%3CP%3Ebut%20better%20all%20constants%20to%20put%20into%20cell%20ranges%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-426768%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20statement%20on%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-426768%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%26nbsp%3B%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%3BThis%20is%20helpful!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-428585%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20statement%20on%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-428585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317288%22%20target%3D%22_blank%22%3E%40Alfredokosun%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Alfredokosun
New Contributor

Hello,

 

I need some help with the IF formula on the attached sheet; I'm not clear if the IF OR statement or IF XOR would do this. Column C has 3 possible entries, column D has 5 possible entries (on a drop-down list). Column E will need a formula, to compare the values on columns C and D, and return a specific value, for the respective cells on column E, depending on the selections of column C and D. The entries are:

 

If column C, marked "Grade" = 6, and column D, marked "Location" = "Lagos" or "Portharcourt" or "Abuja", then, column E should return the value "12500";

if column C, marked "Grade" = 5&4, and column D, marked "Location" = "Lagos" or "Portharcourt" or "Abuja", then, column E should return the value "13500";

if column C, marked "Grade" = 3, and column D, marked "Location" = "Lagos" or "Portharcourt" or "Abuja", then, column E should return the value "18000"

 

If column C, marked "Grade" = 6, and column D, marked "Location" = "Kano" or "Other States", then, column E should return the value "8500";

if column C, marked "Grade" = 5&4, and column D, marked "Location" = "Kano" or "Other States", then, column E should return the value "9500";

if column C, marked "Grade" = 3, and column D, marked "Location" = "Kano" or "Other States", then, column E should return the value "12000"

 

3 Replies

@Alfredokosun , it could be

=IFNA(IF((D4="Lagos")+(D4="Portharcourt")+(D4="Abuja"),LOOKUP(C4,{3,"5&4",6},{18000,13500,12500}),IF((D4="Kano")+(D4="Other States"),LOOKUP(C4,{3,"5&4",6},{12000,9500,8500}),"")),"wrong data")

but better all constants to put into cell ranges

 

Thank you, @Sergei Baklan This is helpful!

@Alfredokosun , glad to help

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies