VLOOKUP to Return Two Users
Hi! I need a formula that looks at two columns and returns an email address based on vlookup if one of the two columns is blank and two email addresses (again based on vlookup) if neither column is blank. The formula works well when one of the columns is blank and only one email address is returned. The problem is when two email addresses are returned Smartsheet doesn't recognize either of them as users.
The values are returned to an Assigned to contact list field with multiple contacts checked.
Here's the formula:
=IF(ISBLANK([Position 2]@row) = true, VLOOKUP([Position1 Div]@row, {Copy of Master Phone List Range 1}, 6, false), VLOOKUP([Position1 Div]@row, {Copy of Master Phone List Range 1}, 6, false) + " " + VLOOKUP([Position2 Div]@row, {Copy of Master Phone List Range 1}, 6, false))
Answers
-
Hey @Liz Castellaw
When I tested the COUNTM on one of my sheets, it seems it needs a line break/carriage return to count ea. CHAR(10) is a linebreak. Try substituting CHAR(10) with no quotes in place of your spaces. Alternatively you could try to use the JOIN function with the CHAR(10)
The JOIN function would look something like this (I haven't tested this)
=IF(ISBLANK([Position 2]@row) = true, VLOOKUP([Position1 Div]@row, {Copy of Master Phone List Range 1}, 6, false), JOIN(VLOOKUP([Position1 Div]@row, {Copy of Master Phone List Range 1}, 6, false), VLOOKUP([Position2 Div]@row, {Copy of Master Phone List Range 1}, 6, false), CHAR(10)))
To see the linebreak, you must have the column wordwrapped.
Let me know if that works for you
Kelly
-
Hi Kelly! Thank you for the suggestion. I thought CHAR(10) would work, but, unfortunately, it returned the same result as the space. Also, I couldn't get JOIN to work as the values aren't from a range. As soon as I typed in the first comma the formula starts looking for the delimiter.
Thanks anyway! (I'll definitely be able to use CHAR(10) in other situations though)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!