 • 512K Members
• 6,120 Online
• 608K Conversations

# 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

# Re: IF statement on MS Excel

@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

# Re: IF statement on MS Excel

Thank you, @Sergei Baklan This is helpful!

# Re: IF statement on MS Excel

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
2 lists, 1 if
Bartosz Heller in Excel on
4 Replies
Copy/paste no longer working in Excel
Jon Firooz in Excel on
76 Replies
Excel If Functions