Index Matching with Multiple values

Molly F.

Hello,

I am index matching and am having success when my franchise contains only one value. However, the franchise column is dropdown list that may contain several values. When the franchise column has multiple values I am receiving #NO MATCH. I am using the formula below. Any suggestions would be appreciated, I am fairly new to SmartSheet and am not able to figure this one our alone.

=INDEX({Region_Column}, MATCH(Franchise@row, {Franchise_Column}, 0))

Thanks in advance.

Molly

Michael Drayton

Hello Molly! by the fact the response is coming back with "#NO MATCH", this means your formula is working correctly. what's not matching is the exact match between what is coming from your dropdown and what is appearing on your franchise list. The Match is case sensitive and spacing sensitive. So if possible, create a helper column in your source sheet to make everything lower case and trim any extra spaces. Call it, "Cleaned Franchise" and use this formula,

=TRIM(LOWER(Franchise@row))

Now back on your destination side use this formula:
=INDEX({Region_Column}, MATCH(TRIM(LOWER(Franchise@row)), {Cleaned Franchise Column}, 0))

Please let me know if this works. Good Luck.

Molly F.

Thank you so much Michael!

I hesitate to even put this here because I am likely missing something really elementary. When adding =TRIM(LOWER(Franchise@row)) to the new "Cleaned Franchise" column is receive #UNPARSEABLE and I do not understand why or how to fix it. 🤦‍♀️

Any advice would be much appreciated.

-Molly