Forum Discussion
How toi create formula using IFS and AND
I am trying to create a formula using IFS to check three possible outcomes: value<5000 - return RM 5000<value<30000 - return VPO value<30000 - return IPC I have managed to get the true value r...
This is a "Nested If" problem. First, let us solve that:
IF (value<5000, "RM", IF (value<30000, "VPO", "IPC"))
Nested IF statements are notoriously hard to maintain and are prone to error.
An easier to maintain and less error prone approach is to leverage Excel's tables like the one below. I called this "tblCodes".
tblCodes
tblCodes is fully dynamic so if we add codes or change values, we can make the changes to the table and leave our formula unchanged. The formula is then:
=VLOOKUP(<value>,tblCodes,2,TRUE)
Where <value> is the cell address or name holding 0, 5000, or 30000. See attached
Thanks for the tip.
After playing around a little longer I found a solution;
=IFS(S22<=5000,"RM",AND(S22>5000,S22<=30000),"VPO",30000<S22,"IPC")