vlookup issue and multiple selections

Jessica Abbey

I'm using a form to collect headcount changes at locations we manage. I'm using a vlookup to autofill the site managers name based on the site location chosen. However, I've run into an issue when the person filling out the form selects more than one location (selected via drop down multi-select list).

If the headcount being reported is shared among 2 sites, those sites will always have the same manager. Is there a way to rewrite the formula so I don't get an error? Maybe only look at one item in the selection rather than both?

This scenario will not happen very often and I thought maybe I could just overwrite the site manager cell in those instances, but I'm using a column formula so the vlookup happens automatically when new entries are made via the form.

Paul Newcome

You need to finish out the second VLOOKUP. Both VLOOKUP functions should be able to function on their own.

VLOOKUP([Site Name]@row, {SM List}, 2, false)

VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1))

In the case of the second one, you stopped after the first argument ([Site Name]@row in the first one).

=IFERROR(1st vlookup, 2nd vlookup)

1st VLOOKUP:

VLOOKUP([Site Name]@row, {SM List}, 2, false)

2nd VLOOKUP:

VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1), {SM List}, 2, false)

Both nested in IFERROR:

=IFERROR(VLOOKUP([Site Name]@row, {SM List}, 2, false), VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1), {SM List}, 2, false))

Paul Newcome

Happy to help. 👍️

Paul Newcome

Try an IFERROR and then repeat the VLOOKUP using a LEFT function to pull the leftmost location.

=IFERROR(VLOOKUP(Location@row, ........................), VLOOKUP(LEFT(Location@row, FIND(CHAR(10), Location@row) - 1), ............................))

Jessica Abbey

I'm getting an incorrect argument error.

Here is my original formula. =VLOOKUP([Site Name]@row, {SM List}, 2, false)

New formula below. I'm not very familiar with the left function. did I leave something out?

=IFERROR(VLOOKUP([Site Name]@row, {SM List}, 2, false), VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1)))

Paul Newcome

You need to finish out the second VLOOKUP. Both VLOOKUP functions should be able to function on their own.

VLOOKUP([Site Name]@row, {SM List}, 2, false)

VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1))

In the case of the second one, you stopped after the first argument ([Site Name]@row in the first one).

=IFERROR(1st vlookup, 2nd vlookup)

1st VLOOKUP:

VLOOKUP([Site Name]@row, {SM List}, 2, false)

2nd VLOOKUP:

VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1), {SM List}, 2, false)

Both nested in IFERROR:

=IFERROR(VLOOKUP([Site Name]@row, {SM List}, 2, false), VLOOKUP(LEFT([Site Name]@row, FIND(CHAR(10), [Site Name]@row) - 1), {SM List}, 2, false))

Jessica Abbey

You are a genius! That worked perfectly. Thanks so much for the help with this.

Paul Newcome

Happy to help. 👍️

BBIrr41

Hi, this post was super helpful. Now what would I need to add to have it return a blank instead of #INVALID VALUE for cells where there is only 1 selection in the multi-select drop down column.

Paul Newcome

@BBIrr41 You would use an IFERROR.

=IFERROR(VLOOKUP(..................), "")