Using lookup formulas with Contact Cells

KPA

Hello,

Trying to sort through an issue I have been having. I am trying to lookup contacts on one sheet against a source sheet to see if they are present. The sheet I am using has the contact in a contact column, but only as an e-mail with no name associated. The source sheet has BOTH the contacts e-mail and name, but by default the contact cell on the source sheet displays the contact name as opposed to the e-mail.

When I perform a lookup between the sheets, I am getting no returns. Is this due to how source sheet defaulting to the contacts name instead of their e-mail? If so is there a way to get the lookup to work without inserting a name for every contact on the target sheet?

jmyzk_cloudsmart_jp

Hi @KPA

The tricky part of using the CONTACT column as a key to use the LOOKUP formula (or INDEX or any other lookup formula) is that the CONTACT can have a one-to-many relationship between an email and names.

For example, the image below shows I assigned three different names for the same email in the Contact column.

So, if I want to look up the Task value using the Contact's name value, we need to first prepare the name-to-email conversion table or sheet, and second, cope with the situation where an email has multiple names in a sheet, as in this example.

Below is the image of the name-to-email conversion table.

https://app.smartsheet.com/b/publish?EQBCT=6af2db3b2c984a00aa9dda775e4e4050

In this example, I copied and pasted the Contact values to this sheet and used the following formula to get the Name and Email.

[Name] =
  LEFT([Copy & Past Contact]@row, FIND("<", [Copy & Past Contact]@row) - 1)

[Email] 
  =MID([Copy & Past Contact]@row, 
      FIND("<", [Copy & Past Contact]@row) + 1, 
      LEN([Copy & Past Contact]@row) - FIND("<", [Copy & Past Contact]@row) - 1)

As explained earlier, an email can be matched with multiple names in the Contact column in Smartsheet. Creating this conversion table is the most challenging part.

This is because the way Contact's name parts are determined involves multiple rules, which an admin cannot control, for example, by the "My Contact List" definition or the cell-by-cell definition, as shown in the example at the top. (for details, look at the Display name of a contact explanation at the bottom.)

https://app.smartsheet.com/b/publish?EQBCT=583ddad78bd74df89f0380414778f475

To retrieve the corresponding Task using Email as a key, we first need to obtain the associated names and use them to retrieve the task.

In the formula below, I created a formula that copes with the case where an email corresponds to three different names.

[Task (First Match)] 
=IFERROR(
  INDEX({Source sheet : Task}, 
    MATCH(
      INDEX({Name to Emai Sheet : Name}, 
      MATCH(Email@row, {Name to Emai Sheet : Email}, 0)), {Source sheet : Contact}, 0
    )
   ), 
"")



[Task (2nd Match)] 
=IFERROR(
  INDEX({Source sheet : Task}, 
    MATCH(
      INDEX(
      COLLECT({Name to Emai Sheet : Name}, {Name to Emai Sheet : Email}, Email@row), 2), 
      {Source sheet : Contact}, 0
    )
  ), 
"")


[Task (3rd Match)] 
=IFERROR(
  INDEX({Source sheet : Task}, 
    MATCH(
      INDEX(
      COLLECT({Name to Emai Sheet : Name}, {Name to Emai Sheet : Email}, Email@row), 3), 
      {Source sheet : Contact}, 0
    )
  ), 
"")

If we do not have to cope with the one-email-to-multiple-names situation, we can simply use the first formula.

I wish Smartsheet would change the specification of the Contact column to restrict it to only one name per email. The flexibility to refer to “Thomas Jefferson” simply as “Jeff” is convenient for day-to-day use, but it complicates formulas because the same email might appear under different display names. If Smartsheet kept the display choice for convenience, but used a single canonical name (from the directory or My Contacts) for formula definitions, it would make things much more consistent.

Display name of a contact

1. Org-level Directory (Account Directory)

• If your Smartsheet organization has an account directory (often linked to SSO or managed by an admin), the display name registered for that email in the org directory is what you’ll see.
• This is the default layer for licensed users inside the same org.

2. My Contacts (personal contact list)

• If you’ve added that email to your own My Smartsheet Contacts with a different display name, your personal setting overrides the org directory name.
• Example: Org directory says Jane Smith, but in your My Contacts you saved that email as Janie S. → you will see Janie S..

3. Cell-level Custom Name

• When you pick or type directly in a Contact column cell, Smartsheet lets you enter a free-form name along with the email.
• That cell-defined name takes precedence over both org directory and My Contacts, for anyone viewing the sheet.
• Example: You type Project Lead <projlead@company .com> into the cell → everyone sees exactly that.

Priority Rule (highest wins)

1. Cell entry (explicit name typed in cell)
2. My Contacts (your personal override)
3. Org Directory (global setting)
4. Fallback: plain email address (if no name found anywhere)