SOLVED

IFS Function

Copper Contributor

Hello, Good night,

 

I'm trying to create a if situation, there's multiple PVC sockets, and for example a 90º knee, the person will choose the size of the socket and will receive the value automatic, for a example:

  • 15 = 1,1
  • 20 = 1,2
  • 25 = 1,5
  • ...
  • 150 = 5,6

So i tried:

=IFS(C3=15;1,1;20;1,2;25;1,5;32;2;40;3,2;50;3,4;60;3,7;75;3,9;100;4,3;125;4,9;150;5,6)

The problem is, only the first two ifs return the propers values, if i choose 25, ill continue returning 1,2, 150 = 1,2, not 5,6. 

 

My excel is a 360, and i tried in the Google version too, both had the same issue, if you have a more easy way to create this situation, i'm happy to hear you.

 

Thanks

2 Replies
best response confirmed by LucasAssis (Copper Contributor)
Solution
The format of IFS() is condition1, output1, condition2, output2, etc... so you would need:
=IFS(C3=15;1,1;C3=20;1,2;C3=25;1,5;....)
OR you can use the SWITCH() function which works almost exactly the way you tried:
=SWITCH(C3;15;1,1;20;1,2;25;1,5;32;2;40;3,2;50;3,4;60;3,7;75;3,9;100;4,3;125;4,9;150;5,6)
OR alternatively you could create a lookup table in your document and then use VLOOKUP() or XLOOKUP(). This alternative is particularly good if you have this formula in many location and maybe 1 day you change 1 of those values.
Thank you! It worked really well.
1 best response

Accepted Solutions
best response confirmed by LucasAssis (Copper Contributor)
Solution
The format of IFS() is condition1, output1, condition2, output2, etc... so you would need:
=IFS(C3=15;1,1;C3=20;1,2;C3=25;1,5;....)
OR you can use the SWITCH() function which works almost exactly the way you tried:
=SWITCH(C3;15;1,1;20;1,2;25;1,5;32;2;40;3,2;50;3,4;60;3,7;75;3,9;100;4,3;125;4,9;150;5,6)
OR alternatively you could create a lookup table in your document and then use VLOOKUP() or XLOOKUP(). This alternative is particularly good if you have this formula in many location and maybe 1 day you change 1 of those values.

View solution in original post