New Contributor

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

Re: How toi create formula using IFS and AND

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"})

Re: How toi create formula using IFS and AND

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

Re: How toi create formula using IFS and AND

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")