SOLVED

New Contributor

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

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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

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

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

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

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