Trying to find values within a dropdown list on another sheet

ckcalvins

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.

Paul Newcome

What would be the expected output? Just a "No" or "Yes" if [Hotel Code]@row is in any cell within the {MLOS_Hotel Code} range? If so, you can use something like this:

=IF(COUNTIFS({MLOS_Hotel Code}, HAS(@cell, [Hotel Code]@row)) > 0, "Yes", "No")

Paul Newcome

What would be the expected output? Just a "No" or "Yes" if [Hotel Code]@row is in any cell within the {MLOS_Hotel Code} range? If so, you can use something like this:

=IF(COUNTIFS({MLOS_Hotel Code}, HAS(@cell, [Hotel Code]@row)) > 0, "Yes", "No")

ckcalvins

The expected output would scan to see if any of the hotel codes were submitted in the form and be a "yes" if found and a "no" if not.

ckcalvins

You did it @Paul Newcome!!

The HAS formula worked across the multiple drop-downs…

=IF(HAS({MLOS_Hotel Code}, [Hotel Code]@row) = true, "Yes", "No")

It will now find multiple hotel codes within one cell as requested. Thanks a million.