Cross-Sheet Reference Syntax - Range vs. SheetName|ColumnName

I'm trying to master Index-Match referencing. To do so I followed the example in SS content-Center - https://www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet

I created the tables used in the example, but ran into syntax problems. The basic formula they give is:

=IFERROR(INDEX({Product Data | Price}, MATCH([Associated Product ID]@row, {Product Data | Product ID}, 0)), "No Match Found")

When I tried to use that I had no syntax errors with {Product Data | Product ID}, but all I got was "No Match Found" results.

When I changed that reference to a range reference {Product Data Range 3}, the formula behaved.

Would someone explain why the {SheetName | ColumnName} syntax didn't work, or how to make it work. I'd much rather use that syntax if possible.

Find more posts tagged with

Accepted answers

All comments

AravindGP

Hi,

I tried simulating your scenario but wasn't able to replicate the error. I tried the same syntax as Product Data | Product ID and Product Data | Price to still get the value without an error as technically, you are free to name the references in anyway you see fit. I usually use "sheet name - reference column name". It might help if you can share a screenshot with the error along with the source sheet screen shot.

Aengineer

Perhaps I'm misunderstanding. Is "Product Data | Price" just a range that has been named that way rather than what I assumed, that the Pipe symbol allows you to put SheetName on the left and ColumnName on the right?

If that's the case, then life makes sense again.

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.