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

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

    PMP Certified

    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.

  • Bassam Khalil
    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

    PMP Certified

    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.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Robert McKnight 

    Ok i will check it and come back to you ASAP

    PMP Certified

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

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!