Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

INDEX / MATCH with Multi-dropdown Lists?

✭✭✭✭
edited 02/02/23 in Formulas and Functions

Is it possible to index and match to a column that is formatted as a multi-dropdown list?

I need to populate a column with an agency name from a column that is a multi-drop down list. When I enter the location on a row I want the adjacent cell to autofill with the agency name.

The agency name (and other info) is on a reference sheet. The locations for each agency are in a multi-dropdown list column because some agencies have multiple locations.

For example in my source sheet if I enter C&O Canal Towpath, I want the adjacent cell to populate with Chesapeake & Ohio Canal National Park.

So is it possible to use INDEX MATCH to accomplish this? Or is there a better way?


Answers

  • Community Champion
    edited 02/03/23

    Hey @mistone

    Be sure that the column you are inserting your formula into is (1) formatted as a multiselect column and (2) formatted as wrap-text. The CHAR(10) is code for a line break. That is the delimiter that separates all multiselect values

    The JOIN will grab all of the data that pertains to your Agency Name and separate them using the defined delimiter.

    =JOIN(INDEX({Reference Sheet Location column}, MATCH([Agency]@row, {Reference Sheet Agency Name column}, 0)), CHAR(10))

    Because these are cross sheet references, you must physically create these references in you sheet - you cannot simply copy paste this formula.

    Will this work for you?

    Kelly

  • ✭✭✭✭

    @Kelly Moore ,

    Thanks for the guidance! So, I built the formula below, and it partially works.

    What's working: The formula will return the land agency name when there is only 1 location name entered in a cell on the reference sheet activity area column. Using the image above the formula will correctly return "Charles County Department of Recreation, Parks, and Tourism" when Mallows Bay is entered on the source sheet.

    What's not working: The formula throws a #NO MATCH error for cells on the reference sheet with multiple activity areas select. Using the image above I'll get a #NO MATCH instead of Chesapeake & Ohio Canal National Park when C&O Canal Towpath is entered on the source sheet.

    =JOIN(INDEX({CUA - Land Agency Col}, MATCH([Activity Location]@row, {CUAs - Activity Area Col}, 0)), CHAR(10))

    If you have any more suggestions on how to proceed, please let me know...

  • Community Champion

    Hey @mistone

    Oh... I see. I know you said what you wanted but it isn't what I pictured. You are entering the multiselects and you want the single name to populate. Typically it's done the other way around. I'll have to test something before I can answer

  • Community Champion

    Hey @mistone

    See if this formula works for you.

    =JOIN(INDEX({CUA - Land Agency Col}, MATCH(INDEX(COLLECT({CUAs - Activity Area Col}, {CUAs - Activity Area Col}, HAS([Activity Location]@row, @cell) = true), 1), {CUAs - Activity Area Col}, 0)))

    Does this formula work for you?

    Kelly

  • ✭✭✭✭

    @Kelly Moore,

    Thanks for continuing to noodle on this! I tired that formula and I get an #INVALID VALUE Error. If you have time, and other suggestions, if not then thanks for your help to this point! I'll keep at it and get it sorted out.

    Cheers,

    Mike


  • ✭✭✭✭

    @Kelly Moore (and those interested...)

    William & Craig, of the rock star pro desk team, assisted me with getting the formula. Here it is:


    Cheers,


    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions