EXCEL FORMULA: I WANT TO AUTO POPULATE A CELL BASED ON THE VALUE OF 2 CULUMNS

[Ascherith]

Hello,

I need help with an excel formula that will auto populate the column PRIORITY LEVEL based on columns CRITICALITY and FREQUENCY LEVEL (see attached screenshot of what should be populated in PRIORITY LEVEL column).
I know that this can be done using IF function, but I can't seem to find the correct one.

I would really appreciate all the help that I can get. Thank you very much in advance!

 

[AlphaFrog]

=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))

 

[Ascherith]

=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))

Thank you very much, it worked perfectly. If you have time to spare, can you kindly explain this code. I can read some codes, but this is a bit confusing for me.

 

[Ascherith]

Thank you very much, it worked perfectly. If you have time to spare, can you kindly explain this code. I can read some codes, but this is a bit confusing for me.

@AlphaFrog sorry, I think there is an issue with the code. I thought it was working perfectly. See the attached. Criticality "MID" and Frequency Level "LOW" became Priority Level "HIGH". This should have been "LOW" only. Can you kindly check on this? Thank you.

 

[Ascherith]

@AlphaFrog sorry, I think there is an issue with the code. I thought it was working perfectly. See the attached. Criticality "MID" and Frequency Level "LOW" became Priority Level "HIGH". This should have been "LOW" only. Can you kindly check on this? Thank you.
View attachment 117347

@AlphaFrog never mind bro. Sorry, I made the mistake, not your formula. Thank you again!

 

[AlphaFrog]

=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))

The 1st IF tests if A7 is Low...
=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))

If that's TRUE, then the 2nd IF tests if C7 is High
=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))
It then returns Mid if TRUE or Low if FALSE. That covers all the possibilities if A7=Low

The 3rd IF is in the FALSE position for the 1st IF (i.e. A7=Low is FALSE). It tests if A7=Mid
=IF(A7="Low", IF(C7="High", "Mid","Low"),IF(A7="Mid",C7,"High"))
If TRUE, it returns whatever is in C7 (that covers all of the possibilities if A7=Mid).
If FALSE, it returns High. A7 must be High because the 1st and 3rd IFs already tested for A7 being Low or Mid. High is the only result if A7 is High regardless of what is in C7.