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.

Answers

  • AravindGP
    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!