IFS not working. Why?

As I understand it, IFS() works from left to right until the first TRUE result from the logic test, then returns the corresponding value in the next argument. In this case, all of the non-zero values are greater than 91 (even if they are also greater than a subsequent logic test), so it returns 20.

At 5 conditions, I would be inclined to use a lookup function. In K4:K8, enter 91, 151, etc. (the lower bound from the text in column L), then use something like =VLOOKUP(D4,$K$4:$M$8,3,TRUE). TRUE in the 4th argument causes VLOOKUP() to perform an "approximate match" search which is often missing from most tutorials (this tutorial does not neglect to explain the behavior of TRUE: https://www.ablebits.com/office-addi...ximate-vlookup ).

If you still prefer to use IFS(), then you will need to arrange the lookup tests so they either are less than max value of range [IFS(D4<91,"out of range",D4<151,20,D4<281,32,...)] or arrange the greater than arguments in descending order [IFS(D4>1200,125,D4>501,80,D4>281,50,...)]. Just remember that IFS() tests from left to right until the first TRUE test.

Try:

Formula:

Please Login or Register  to view this content.

Note that numbers in quotes are text values and not numeric. "20" is NOT the same as 20.

You're welcome.

I don't think this does what you think it does … it's very specific: F18<>2300,125. It's saying, If F18 is not equal to 2300, then return 125.




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Ok. And then you probably need to put back the TRUE, "out of range" bit.