Cell Reference, Contact List/Text

I have two columns in my sheet, both are Contact List columns and both reference columns in another sheet that are also both Contact List. Under Program Developer (below), you'll see the formula returns just the name, but under Program Manager, it returns the contact.

I have essentially the same formula in both:

Program Developer

=IFERROR(INDEX({ProgramCodes-ProgramDeveloper}, MATCH(VALUE([Program Code]@row ), {ProgramCodes-ProgramCode}, 0)), "No Match")

Program Manager:

=IFERROR(INDEX({ProgramCodes-ProgramManager}, MATCH(VALUE([Program Code]@row ), {ProgramCodes-ProgramCode}, 0)), "No Match")

Why is one just a name, and the other in a contact? Incidentally, I tested putting the formula for Program Manager in the Program Developer column, and it still shows them as Contacts, not just text.

Find more posts tagged with

Accepted answers

All comments

Jovana.Perkovic

Hi @aschneiderheinze1025 ,

are both column types in Source sheet(where you pulling formula from) also 'Contact List'?

If both columns are set to Contact List, the difference comes from the data in the source sheet.

If the source column contains actual Smartsheet contacts (name + email), the formula will return a contact object.
If the source column only has plain text names (not linked to Smartsheet contacts), the formula will return just the name.

So in your case, the Program Manager column in the source sheet likely has real Smartsheet contacts, while Program Developer has plain text names.

Fix:

Make sure both source columns have actual Smartsheet contacts (not just text).
If you want names only, convert the destination column to Text/Number or use + "" at the end of the formula to force text:

=IFERROR(INDEX({ProgramCodes-ProgramManager}, MATCH(VALUE([Program Code]@row ), {ProgramCodes-ProgramCode}, 0)) + "", "No Match")

That should make it consistent. Let me know if this worked for you! :)

Paul Newcome

While the Program Developer column in the source sheet may be a contact type column, double check the data that is in that column.

The INDEX function retains data types, so if it is pulling text, that means the source data type is also text. Correcting this in your source sheet should fix the issue.

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.