Help on an IFS statement please

[Integrity_Drywall]

I am trying to write an IFS statement to make life easier while using an Excel sheet at work.  I was told there is no limit on the amount of data in the IFS statement but I keep getting an error and I am not sure if I am doing the statement wrong or if it is just to large and I cant have it do what I want it to do.  Basically what I am trying to do is have the square footage column auto populate based on the model name when you enter it.  Can someone please help.  The error is :  

Error  Formula parse error

The statement I am trying to us is:  

=IFS(D32="136 PARROT", 1940, D32="175 CABANA", 2393, D32="175 II CABANA BAY”, 2393, D32="176 BREEZE”,2083, D32="176 II BREEZE BAY”,1616, D32="177 COCONUT”,1685, D32="178 HAMMOCK”,2114, D32="178 II HAMMOCK BAY”,2114, D32="179 TRINIDAD”,2568, D32="179 II TRINIDAD BAY”,2549, D32="181 ST. BART”,2492, D32="184 ARUBA”,2336, D32="169 ANTIGUA”,1565, D32="171 BARBUDA”,1787, D32="171 II BARBUDA BAY”,0, D32="172 LUCIA”,1773, D32="173 NEVIS”,1865, D32="183 JAMAICA”,1507, D32="195 TORTOLA”,1743, D32="189 ALOHA”, 1466, D32="190 BAMBOO”, 1204, D32="191 CAMELLIA”,1321, D32="192 DREAMSICLE”,1378)")

Any help would be greatly appreciated.

Thank you

 

[#2]

@Integrity_Drywall 

=IFS(D32="136 PARROT", 1940, D32="175 CABANA", 2393, D32="175 II CABANA BAY", 2393, D32="176 BREEZE",2083, D32="176 II BREEZE BAY",1616, D32="177 COCONUT",1685, D32="178 HAMMOCK",2114, D32="178 II HAMMOCK BAY",2114, D32="179 TRINIDAD",2568, D32="179 II TRINIDAD BAY",2549, D32="181 ST. BART",2492, D32="184 ARUBA",2336, D32="169 ANTIGUA",1565, D32="171 BARBUDA",1787, D32="171 II BARBUDA BAY",0, D32="172 LUCIA",1773, D32="173 NEVIS",1865, D32="183 JAMAICA",1507, D32="195 TORTOLA",1743, D32="189 ALOHA", 1466, D32="190 BAMBOO", 1204, D32="191 CAMELLIA",1321, D32="192 DREAMSICLE",1378)

It works in my excel online sheet after replacing ” by " and changing the end of the formula 1378)") to 1378) .

[#3]

@Integrity_Drywall  first off the error appears to be at the end:

...., D32="192 DREAMSICLE”,1378)")

That final ") doesn't match anything

That said I highly suggest you create a table with the values you want and use a simple LOOKUP instead of this large IFS statement.  BTW, another option in this case would have been to use SWITCH but a LOOKUP table would still be better IMHO

[#4]

THANK YOU!!!!

[#5]

I really appreciate the help. I am new at this and dont know how to use switch or a look up.

[#6 · Accepted solution]

@Integrity_Drywall  In the attached i created a table with your model names and sq.ft. info and then to the right I have a drop down to select a model and then in the next cell I use VLOOKUP to lookup the sq.ft. for the model you selected.  I hope it helps you learn/understand how to use a LOOKUP.  There are also innumerous resources on the web.  I highly recommend this method as it makes your life much easier.  The formula is much smaller and easier to understand (once you understand LOOKUP) and you can easily update/change the information in the table and not have to search every formula. 

[#7]

thank you. that seems so much easier.

[#8]

@Integrity_Drywall to underscore what my friend@mtarler has said, the reason the LOOKUP approach works better in the long term, is that what you were doing is what's called "hardcoding of variables." You were building variables into the formula itself, a practice that though it can work in the short term, makes any formula vulnerable when, in keeping with the meaning of the term "variable," one or more of those variables actually varies, changes...

Using a table, on the other hand, keeps the formula robust, and allows you to change the variables with impunity.