VLOOKUP for multiple values



I'm using JOIN COLLECT to look up a variable's name in a block of text. However, I am also trying to use a VLOOKUP to change the found variable name in the JOIN COLLECT column to the standardized name I need it to be for other calculations. For instance, the JOIN COLLECT finds "Annual Miles" but I want it to be changed to "Mileage." I created an additional column.. right now named "Test" that pulls out the values I want to change .. but the VLOOKUP I created will only look for the first value.

Vlookup formula in the Variable Match column is: =IFERROR(IF(test@row = "", "", VLOOKUP(JOIN(COLLECT({LSC Variables of Concern VLOOKUP Range 4}, {LSC Variables of Concern VLOOKUP Range 4}, HAS(@cell, test@row))), {LSC Variables of Concern VLOOKUP Range 3}, 2, false)), "")

But it only appears to work if there's only 1 variable in the 'test" column

In the attached image the VLOOKUP worked on the row with "Mileage" in the Variable Match column but did not work in the other rows with multiple values in the test column.

I hope this makes sense!


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Christa Brown 

    Hope you are fine, if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

    PMP Certified


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

  • Jaykel Torres

    Hey @Christa Brown,

    In reviewing your formula, it appears you are evaluating a Dropdown (Multi-Select) Column against a Text/Number Column using the HAS Function; this is possible but only when accounting for a single parameter (ex. Annual Miles). I believe you are looking for a way to account for multiple values in a single cell (ex. Vehicle Use, Years Owned Vehicle) but since this is a text/number Column it is treating the Cell Value as a singular string of text.

    The best way to resolve this, is to ensure that both columns are Dropdown Multi-Select types.

    I hope this helps!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!