Trying to find values within a dropdown list on another sheet


So I'm trying to to "find" if a value has been submitted in a form where (for ease of form submission) submitters can submit the form for multiple properties.

I'm not sure if it's possible without arrays, but seeing if anyone could crack this for me…

My formula on the target sheet is this:

It's trying to find a hotel code from the source sheet, from a column where multiple hotel codes can (but not always) be chosen. See the source sheet form column below…

The formula works as expected when only one hotel code exists, but if two exist, it breaks. It makes sense why it breaks, but I'm seeing if anyone has any ideas…

There could be like 20 hotel codes chosen, so creating helper columns to parse out isn't really an option. I'd rather not allow multiples in the drop-down and make people submit the form multiple times, unfortunately.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!