VLOOKUP on a Multi DropDown List

I'm attempting to setup an asset tracking system using Smartsheets, where individuals fill out a form requesting specific assets, in which they'd be assigned in the sheet using a multi select dropdown of all the asset barcodes.

In another sheet I have all the assets accounted for, each in their own row. I'm trying to use VLOOKUP in this sheet to look across the assigned column in Sheet 1 to then fill relevant information about each asset (who it's been loaned to, etc). This works when there is only one value in the cell, however should anyone pick more than one dropdown value VLOOKUP returns a NO MATCH.

Any ideas on how to accomplish?

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Robert McKnight

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Robert McKnight

@Bassam Khalil thanks for the reply.

Here is the first sheet, the second column is the multi dropdown for barcodes - in this case two assets have been assigned to this User

=IFERROR(VLOOKUP(Barcode@row, {Current Hard Drive Requests Range 1}, 9, false), "")

If only one asset is selected in the drop down VLOOKUP runs successfully, however if there are any additional options selected VLOOKUP doesn't return the value.

Bassam Khalil

Hi @Robert McKnight

Hope you are fine if it's possible to make a test workspace and save a copy of your sheets with sample data ( remove any sensitive data ) and share me as an admin to help me to create the exact formula for you then you can copy that formula to your original sheets.

My Email: Bassam.khalil2009@gmail.com

Robert McKnight

Bassam just shared with you the workspace. Let me know if you want to connect.

Bassam Khalil

@Robert McKnight

Ok i will check it and come back to you ASAP

Bassam Khalil

Hi @@Robert McKnight

Hope you are fine, i changed your current formula

=IFERROR(VLOOKUP(Barcode@row, {Current Hard Drive Requests Range 1}, 9, false), "")

with the following formula to solve the problem of pick more than one dropdown value.

=JOIN(COLLECT({Responsible Owner(s)}, {Loaned Drive}, CONTAINS(Barcode@row, @cell)))

please check the workspace you shared with me. and don't hesitate to call if you have more issues.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.