Formula works for 2 out of 3 situations, how do I fix the 3rd?!

[hoakford]

Hi Team,

I am having a bit of a nightmare with a formula, the one I am using is an =IFS, and for the first two logical test/value if true, it works absolutely fine, but for whatever reason it doesn't work for the 3rd. 

The formula I am using is below:

=IFS(D11<=12,E17,D11>=13,E18,D11<20,E18,D11>=21,E19)

The last one (D11>=21,E19) is still pulling the data from E18. Please can anyone help? I'm sure it's something small, but I've looked around and tried everything I can think of! 

TIA

[#2 · Accepted solution]

@hoakford You have to change the logic of the arguments. For instance, number 18 is both greater than 13 and less then 20, so the formula will return the value for the first match.

What you need is this;

=IFS(D11>=21,E19,D11>=13,E18,D11<=12,E17)

or as a variant, this one will also work:

=IFERROR(IFS(D11>=21,E19,D11>=13,E18),E17)

[#3]

@Riny_van_Eekelen INCREDIBLE! thank you so much!! I knew it would be something small.