VLOOKUP on a Multi DropDown List

04/30/21
Answered - Pending Review

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

  • Bassam KhalilBassam 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.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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([email protected], {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 KhalilBassam 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: [email protected]

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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.

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @Robert McKnight 

    Ok i will check it and come back to you ASAP

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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 KhalilBassam Khalil ✭✭✭✭✭

    Hi @@Robert McKnight 

    Hope you are fine, i changed your current formula

    =IFERROR(VLOOKUP([email protected], {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([email protected], @cell)))

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

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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"

Sign In or Register to comment.