Formula needed to fill a cell if a word is contained as part of another cell

SSParks

I have a column named Assigned Staff that is a drop down with staff names. ex. Mike Duplay and Amanda Perry. I have another column named Assigned Amanda. I need Assigned Amanda column to auto fill the word Amanda Perry if (her name) is contained in Assigned Staff. The issue Im running into is if Mike Duplay and Amanda Perry are BOTH selected in Assigned Staff, I dont know how to auto fill a word if both are selected not just Amanda Perry.

Below is what I tried with no luck

=IF(ISNUMBER(SEARCH("Amanda Perry", [Assigned Staff]@row)), "Amanda Perry", "")

Any help is appreciated.

This works ONLY if her name is the ONLY name selected

=IF([Assigned Staff]@row = "Amanda Perry", "Amanda Perry")

Kelly Moore

Hey @SSParks

When using multiselect fields, you must use the functions HAS, CONTAINS or FIND to tease out specific values.

Try this. You will need a specific formula in the respective column

=IF(HAS([Assigned Staff]@row, "Amanda"), "Amanda Perry")

Will that work for you?

Kelly

SSParks

Interesting - didnt work until I added her last name. This will work for me (Thank you!) but should it have picked up her first name and worked?

=IF(HAS([Assigned Staff]@row, "Amanda Perry"), "Amanda Perry")

Kelly Moore

Hey @SSParks

HAS is an exact match. Sorry, I wasn't paying attention that the screenshot was truncated. CONTAINS would catch a portion of the word

https://help.smartsheet.com/function/has

Kelly

SSParks

Taking this a step farther (if possible). I have a column PARK LOCATION with a drop down of different parks. Id like the column PARK LOCATION ADDRESS to auto-fill the address based on what park has been selected in the PARK LOCATION column. Only one location will be selected. Below is the formula for 1 location but how do I have it populate different addresses for different parks? This works for Cooks Lagoon but what if Deer Creek is selected? =IF(HAS([Park Location]@row, "Cooks Lagoon"), "1819 Mahoning Rd NE, Canton, OH 44705")

Genevieve P.

Hey @SSParks

There are two ways of doing this:

• A nested IF statement for every possibility
• A reference sheet and an INDEX(MATCH lookup

This is an example of the IF statement:

=IF([Park Location]@row = "AAA", "AAA Address", IF([Park Location]@row = "BBB", "BBB Address", IF([Park Location]@row = "CCC", "CCC Address".... and so on

Here's a link to a Help Article that explains how to create an INDEX(MATCH combination if you would prefer to store all your addresses in a second sheet, instead: Lookup one cell using a matching value

Cheers,

Genevieve

SSParks

Thank you, you rock! this was my final code

=IF([Park Location]@row = "Cook's Lagoon", "1819 Mahoning Rd NE, Canton, OH 44705", IF([Park Location]@row = "Deer Creek", "14514 Price St NE, Alliance, OH 44601", IF([Park Location]@row = "Devonshire", "4679 South Blvd NW, Canton, OH 44718", IF([Park Location]@row = "Esmont", " 194 Mill St. SE, Canton, OH 44707", IF([Park Location]@row = "Fichtner", " 12833 Market Ave. Hartville, OH 44632", IF([Park Location]@row = "Fry", " 2533 Farber St. SE, Magnolia, OH 44643", IF([Park Location]@row = "Magnolia Mill", " 261 Main St., Magnolia, OH 44643", IF([Park Location]@row = "Petros", " 3519 Perry Dr SW, Canton, OH 44706", IF([Park Location]@row = "Quail Hollow", " 13480 Congress Lake Avenue, Hartville, OH 44632", IF([Park Location]@row = "Sippo Lake Marina", " 5300 Tyner St., Canton OH 44708", IF([Park Location]@row = "Sippo Lake Exploration Gateway", " 5712 12th Street NW, Canton OH 44708", IF([Park Location]@row = "Tam O’Shanter", " 5055 Hills & Dales Rd. NW, Canton, OH 44708", IF([Park Location]@row = "Walborn", " 11324 Price St. NE, Alliance 44601", IF([Park Location]@row = "Wildlife Conservation Center", " 800 Genoa Ave NW, Massillon, OH 44646"))))))))))))))