vlookup issue and multiple selections
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.
Best Answers
-
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))
-
Happy to help. 👍️
Answers
-
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), ............................))
-
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)))
-
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))
-
You are a genius! That worked perfectly. Thanks so much for the help with this.
-
Happy to help. 👍️
-
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.
-
@BBIrr41 You would use an IFERROR.
=IFERROR(VLOOKUP(..................), "")
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives