Getting an error by using IFS function in google sheets

Question

Just try "IFS" with "ARRAYFORULA" & "AND" function to convert units but getting an error message.

Trying the following formula to convert some units. Image is attached of function

=ARRAYFORMULA(IfS(AND(G2="inches",G3="CM"),CONVERT(H2:J2,"in","cm"), AND(G2="inches",G3="mm"),CONVERT(H2:J2,"in","mm"), AND(G2="inches",G3="Meter"),CONVERT(H2:J2,"in","m"), AND(G2="cm",G3="mm"),CONVERT(H2:J2,"cm","mm"), AND(G2="cm",G3="inches"),CONVERT(H2:J2,"cm","in"), AND(G2="cm",G3="Meter"),CONVERT(H2:J2,"cm","m"), AND(G2="mm",G3="cm"),CONVERT(H2:J2,"mm","cm"), AND(G2="mm",G3="inches"),CONVERT(H2:J2,"mm","in"), AND(G2="mm",G3="Meter"),CONVERT(H2:J2,"mm","m"), AND(G2="Meter",G3="inches"),CONVERT(H2:J2,"m","in"), AND(G2="Meter",G3="cm"),CONVERT(H2:J2,"m","cm"), AND(G2="Meter",G3="mm"),CONVERT(H2:J2,"m","mm"), AND(G2="pound",G3="kg"),CONVERT(H2,"lbm","kg"), AND(G2="kg",G3="pound"),CONVERT(H2,"kg","lbm")))

ERROR: "IFS HAS MISMATCH RANGE"

rockinfreakshow · Accepted Answer · 2024-07-24 07:57:45Z

Try adapting your ifs() version into simple if(); the below is a sample excerpt of how it should be framed:

=arrayformula(If(AND(G2="inches",G3="CM"),CONVERT(H2:J2,"in","cm"), If(AND(G2="inches",G3="mm"),CONVERT(H2:J2,"in","mm"), If(AND(G2="inches",G3="Meter"),CONVERT(H2:J2,"in","m"), If(AND(G2="cm",G3="mm"),CONVERT(H2:J2,"cm","mm"))))))

Here's some possible explanation on ifs() backend behavior which usually leads to the mismatched range sizes error

Yes, I'm already using if() function and it's working perfectly. I'm just curious about if I can convert into ifs() function but failed.
the answer has a link to the explanation(with examples and screenshots) on why ifs() is not ideal for this scenario & throws this specific error

Collectives™ on Stack Overflow

Getting an error by using IFS function in google sheets

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related