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 return for the first and the last logical test, while the middle where it checks for value above and below returns N/A
Tried adding the use of AND in the middle but still returns N/A
Anyone?
3 Replies
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")
Let's say the value is in A2. The formula (in another cell) could be
=IFS(A2<5000,"RM",A2<30000,"VPO",A2>=30000,"IPC")
or
=IF(A2<5000,"RM",IF(A2<30000,"VPO","IPC"))
or
=LOOKUP(A2,{0,5000,30000},{"RM","VPO","IPC"})
