Skip to main content

Hello!


I am a programming coordinator at a library, managing programming for 3 different branches with multiple different rooms. I’m trying to create a formula to pull from a “room” field into another that will formulate the “address” for the building the room is in. Example: Room X, Room Y, and Z should put Address 1 in the formula column, but Room A and B are at Address 2, Room C is at Address 3. I am able to use an IF formula to get it to work for 1 room, but anytime I try and add others I get errors.


Here’s what I am working with so far:


IF(Room = “HPL Program Room”, “Address 1”, “Address 2”)


This will give me Address 1 for the “HPL Program Room” records, and address 2 for anything that is not “HPL Program Room.” If I try adding other conditions, I get errors. How do I add the other circumstances to the same string/formula field?

Hi @Annie_Tillmann,


Since each condition you need to check is referencing the same field (Room), I’d suggest using a SWITCH() function for this:


It would look something like this:


SWITCH(

   {Room},

   "HPL Program Room", "Address 1",

   "Room A", "Address 2",

   "Room C", "Address 3",

   "Room B", "Address 2"

)

and you can continue adding your list of "Room", "Address" combinations inside that function. Make sure to add a comma after each address except the very last one in the list.

Hi @Annie_Tillmann - you can use a nested IF formula, although a SWITCH formula might work better in this case:


SWITCH(Room,

 'Room 1', 'Address 1',

'Room 2', Address 2'

)

JB

Hi @Annie_Tillmann - you can use a nested IF formula, although a SWITCH formula might work better in this case:


SWITCH(Room,

 'Room 1', 'Address 1',

'Room 2', Address 2'

)

JB


Thank you, this worked wonders!

Hi @Annie_Tillmann - you can use a nested IF formula, although a SWITCH formula might work better in this case:


SWITCH(Room,

 'Room 1', 'Address 1',

'Room 2', Address 2'

)

JB


This was VERY helpful. Thank you. What would I include for “ALL OTHER VALUES” that would return a 0? Thank you @JonathanBowen

This was VERY helpful. Thank you. What would I include for “ALL OTHER VALUES” that would return a 0? Thank you @JonathanBowen


@Niels_Schneider, you can include a “default” value as the final argument to a SWITCH() function. The function will fall back to the “default” value if no match is made in the SWITCH() function. So an example would look like this:


SWITCH(

   {Room},

   "HPL Program Room", "Address 1",

   "Room A", "Address 2",

   "Room C", "Address 3",

   "Room B", "Address 2",

   "Default value"

)

@Niels_Schneider, you can include a “default” value as the final argument to a SWITCH() function. The function will fall back to the “default” value if no match is made in the SWITCH() function. So an example would look like this:


SWITCH(

   {Room},

   "HPL Program Room", "Address 1",

   "Room A", "Address 2",

   "Room C", "Address 3",

   "Room B", "Address 2",

   "Default value"

)

Thank you! My table is complete!

Hi everyone,

Terrific discussion here. I was curious - is there a way to provide an alternative result for each chosen value if the field it’s populating from is BLANK()? Each one of my choices in the single dropdown needs to populate data from another field (an email address) — but if the email address field is empty, I need to enter something like “Please enter a child’s email address” (If they choose child and child email address field is empty) or “Please enter a parent’s email address” (If they choose parent and parent email address field is empty)

Reply


Terms & Conditions