INDEX / MATCH with Multi-dropdown Lists?

mistone

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?

Kelly Moore

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

mistone

@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...

Kelly Moore

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

Kelly Moore

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

mistone

@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

mistone

@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