Forum Discussion
Structured References
I have a table that is referencing tables from multiple other sheets. The first formula picks up the structured reference, but each row below that when I try to reference the table in the next sheet...
Tables (otherwise known as list object) do not provide any form of structured reference notation for other records in the list. That is because a list is inherently an unordered collection of records that may be sorted at any time. Concepts like first, last, prior, next are meaningless when a list may be sorted according to various criteria (unlike arrays which are ordered and elements are referenced by index).
Lookups between Tables are best achieved through the use of foreign keys pairing to the primary key of the Table. To return the record with a primary key that matches 'foreignKey' one would have
= XLOOKUP(foreignKey, Table1[key], Table1)
= XLOOKUP(foreignKey, Table1[key], Table1[data])
The second returns a specific field within the record.