# Complex Switch using AND?

Topic Labels: Formulas
Solved
1088 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hello!  I'm relatively new to AirTable, we're migrating here from FileMaker Pro.  I'm forced to duplicate a calculated field in FileMaker, which is essentially the same a a Formula in AirTable.  Basically it does some complex looking at a zipcode value and either displays a Metro Region if it's a certain zipcode, or instead, just displays the City name.

I've been trying to do this:

SWITCH(
AND({l_zip} >= 94100, {l_zip} <= 94199), "North/San Francisco",
AND({l_zip} >= 93600, {l_zip} <= 96199), "North/Other Cities",

{l_zip} = 91604, "South/Los Angeles & Vicinity",
{l_zip} = 91711, "South/Los Angeles & Vicinity",
{l_zip} = 90755, "South/Los Angeles & Vicinity",

AND({l_zip} >= 90000, {l_zip} <= 90199), "South/Los Angeles & Vicinity",
AND({l_zip} >= 90200, {l_zip} <= 90599), "South/Los Angeles & Vicinity",
AND({l_zip} >= 90800, {l_zip} <= 90899), "South/Los Angeles & Vicinity",
AND({l_zip} >= 91100, {l_zip} <= 91199), "South/Los Angeles & Vicinity",
AND({l_zip} >= 91500, {l_zip} <= 91599), "South/Los Angeles & Vicinity",

But it appears that SWITCH does not like having ANDs in the match part of the statement.  How would I do something like this?  I need all zipcodes between 90800 and 90899 to say "South/Los Angeles & Vicinity", for example.

Thanks so much!

1 Solution

Accepted Solutions
11 - Venus

Hello @dvanhook ,

Formulas cannot be used for values evaluated by the Switch function.

``````IF(NOT(l_country),
SWITCH({l_zip},
91604, "South/Los Angeles & Vicinity",
91711, "South/Los Angeles & Vicinity",
90755, "South/Los Angeles & Vicinity",
IF(
AND({l_zip}>= 94100, {l_zip}<= 94199),
"North/San Francisco",
{l_city}
)
)
, {l_city}
)``````

4 Replies 4
5 - Automation Enthusiast

Duh, sorry, I cut and pasted from an earlier incorrect version.  Here's my code, which is improved but still not working:

IF(NOT(l_country),
SWITCH({l_zip},
91604, "South/Los Angeles & Vicinity",
91711, "South/Los Angeles & Vicinity",
90755, "South/Los Angeles & Vicinity",
AND(>= 94100, <= 94199), "North/San Francisco",
l_city
)
, l_city)
18 - Pluto

I recommend using nested IF() functions instead of SWITCH().

11 - Venus

Hello @dvanhook ,

Formulas cannot be used for values evaluated by the Switch function.

``````IF(NOT(l_country),
SWITCH({l_zip},
91604, "South/Los Angeles & Vicinity",
91711, "South/Los Angeles & Vicinity",
90755, "South/Los Angeles & Vicinity",
IF(
AND({l_zip}>= 94100, {l_zip}<= 94199),
"North/San Francisco",
{l_city}
)
)
, {l_city}
)``````

5 - Automation Enthusiast

Thanks everyone — looks like nested IF statements are the way to go!