User Email via vlookup formula working intermitently

I have a Smartsheet with a column that references, through vlookup, another Smartsheet that returns the email address (all Smartsheet users) of the person tied to that row (relationship is based on department # in another column). The formula works great, and returns the email address correctly everytime, however, it only seems to return the email address recognized as a Smartsheet user some of the time. In other words, it always returns the correct email address, but what I need it to do every time is return the email address and have it be recognized as a Smartsheet user (signified by the user's initials as an icon to the right of their email address). I have not pinpointed what is causing this to work intermittently - this happens to the same person within the list, and the same exact vlookup formula is being used in every cell. See screenshot example below, where the same email address is being recognized as a Smartsheet user address ("J" icon), and the three above it are not. I have filtering that is based upon the logged in user, and it does not work properly for the rows where the email address is not recognized as a SS user email. Any suggestions to address this issue are welcome! TIA!

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Jacen Miller

Are both of the columns set to be Contact Type of columns? (The one from the source sheet as well as this one, in the destination sheet).

I believe @Bassam.M Khalil is referring to ensuring that the contact is a part of your Contacts, with a first and last name (instead of just the email address). It may help the formula to recognize that this is a contact that needs to be pulled through, versus just an email.

I've seen this behaviour before if the source sheet has the column set as a Text column with the email and it's being pulled into a Contact Column. Sometimes the sheet needs to be saved for it to recognize that the text email is actually a contact.

All comments

Bassam Khalil

Hi @Jacen Miller

Hope you are fine, did you check that if you add the first and last name for each user in your user list "User Management" tab.

Jacen Miller

Thank you for your reply. - can you be more specific? Not sure what the Uswer Management tab is, and the Smartsheet with the list of "users" that brings back their email address via the vlookup formula has the exact same email address listed for each of their reference data - ie, each user has multiple matching criteria tied to their email address (department # for example - some users have more than one department), so the formula is bringing back the correct user email address every time for each row in the Smartsheet, but it is only recognizing the email address as a Smartsheet User email address some of the time - again, see the screenshot in the original post. In this example, there are six consecutive rows in the Smartsheet referencing the same department for that user, and half of the rows are recognized as Smartsheet user email addresses, and the other half are not, which makes the filter utilizing the logged-in user criteria inaccurate, as it is only capturing some of the rows.

Genevieve P.

Hi @Jacen Miller

Are both of the columns set to be Contact Type of columns? (The one from the source sheet as well as this one, in the destination sheet).

Jacen Miller

Genevieve - this was exactly the issue! Thank you. Did not seem to create an issue until recently which is why I overlooked this detail.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.