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))

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Moore

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

Liz Castellaw

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.