Forum Discussion
IF statement on MS Excel
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, SergeiBaklan This is helpful!
Alfredokosun , glad to help
