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

Aengineer

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.

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.