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?
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@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.
-
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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam just shared with you the workspace. Let me know if you want to connect.
-
Ok i will check it and come back to you ASAP
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!