Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 22, 2019 12:58 PM
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?
Solved! Go to Solution.
Apr 22, 2019 01:02 PM
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.
Apr 22, 2019 01:02 PM
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.
Apr 22, 2019 01:03 PM
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
Apr 22, 2019 01:14 PM
Thank you, this worked wonders!
Jan 20, 2020 10:39 PM
This was VERY helpful. Thank you. What would I include for “ALL OTHER VALUES” that would return a 0? Thank you @JonathanBowen
Jan 21, 2020 09:15 AM
@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"
)
Jan 21, 2020 10:36 AM
Thank you! My table is complete!
Jul 21, 2020 11:05 AM
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)