VLookup from multi select "Search Value"

Deonizia Egan

=VLOOKUP(Preceptor@row, {VLookUp for Preceptors MCE Name}, 5, false)

Trying to pull one answer from a search value that could have multiple options. Preceptor@row is a multi select drop down, and I only need to pull one value from any of the possible options in the preceptor@row (because it will be the same no matter who gets searched)

The reference sheet is NOT a multi select dropdown (does that make a difference?)

SSFeatures

Hi,

If you use a multi-select dropdown, SmartSheet gives you the value but adds a new line in between each selected option. This new line is represented by CHAR(10).

So, if we want to find the first selected option in a dropdown, we can do this:

=LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row))

However, this formula only works if there are at least 2 options selected. If there's only 1 option selected then FIND will be 0, so LEFT will return 0 characters. Let's fix this:

=IF(FIND(CHAR(10), [Preceptor]@row) > 0,

    LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row)),

    [Preceptor]@row)

This formula checks if there are multiple selected. If there are multiple selected then Find CHAR(10) and return all of the characters to the left (which is the first selected value). If there is only a single selected, then return that value.

Now let's update your VLOOKUP to

=VLOOKUP(

    IF(FIND(CHAR(10), [Preceptor]@row) > 0,

        LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row)),

        [Preceptor]@row),

    {VLookup for Preceptors MCE Name},

    5,

    false

)

This should solve the problem for you!

SSFeatures

SSFeatures

Hi,

If you use a multi-select dropdown, SmartSheet gives you the value but adds a new line in between each selected option. This new line is represented by CHAR(10).

So, if we want to find the first selected option in a dropdown, we can do this:

=LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row))

However, this formula only works if there are at least 2 options selected. If there's only 1 option selected then FIND will be 0, so LEFT will return 0 characters. Let's fix this:

=IF(FIND(CHAR(10), [Preceptor]@row) > 0,

    LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row)),

    [Preceptor]@row)

This formula checks if there are multiple selected. If there are multiple selected then Find CHAR(10) and return all of the characters to the left (which is the first selected value). If there is only a single selected, then return that value.

Now let's update your VLOOKUP to

=VLOOKUP(

    IF(FIND(CHAR(10), [Preceptor]@row) > 0,

        LEFT([Preceptor]@row, FIND(CHAR(10), [Preceptor]@row)),

        [Preceptor]@row),

    {VLookup for Preceptors MCE Name},

    5,

    false

)

This should solve the problem for you!

SSFeatures