VLOOKUP for multiple values

Christa Brown

Hello!

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

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.

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!

Jaykel