How toi create formula using IFS and AND

Copper Contributor

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

@kkrakenes 

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

@kkrakenes , 

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

tblCodestblCodes

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

@Craig Hatmaker 

 

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