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!


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.

Answers

  • Bassam Khalil
    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!