Forum Discussion
XLookup with Structured Table References
I can't find anything that directly speaks to this, so I'll post here.
I have a table of individuals with some columns of information. It's defined as as a table.
I have another table where I would like to use XLOOKUP to search that table and return some information from it.
Cells in the second table have references like this:
=XLOOKUP([@Name],MasterlistTable[@Name],MasterlistTable[@[Phone 1]],"error")
They all return a "value not available" error.
I've copied that sheet to another and changed the table references to use named ranges instead.
=XLOOKUP($A2,Name,Phone1,"error")
Those work fine.
Can anyone shed light on what's going on here? I uploaded the simple workbook with the two sheets.
I would love to use xlookup to reference the data in the table and take advantage of its simplicity and power.
Bert Onstott If those are real names and info, please delete and only supply phony informations.
As for what is happening is the '@' which is saying look only at this specific row. So change it to this and it seems to work fine:
=XLOOKUP([@Name],MasterlistTable[Name],MasterlistTable[Phone 1],"error")so now it looks at the Name in this table on THIS (@) line, but searches the entire MasterlistTable[Name] instead of only 1 row of that table.
3 Replies
Bert Onstott If those are real names and info, please delete and only supply phony informations.
As for what is happening is the '@' which is saying look only at this specific row. So change it to this and it seems to work fine:
=XLOOKUP([@Name],MasterlistTable[Name],MasterlistTable[Phone 1],"error")so now it looks at the Name in this table on THIS (@) line, but searches the entire MasterlistTable[Name] instead of only 1 row of that table.
- Thank you so much!!! I am new to using XLOOKUP and did not know about this @ symbol for table structure. This saved me!!
Thanks, that worked!
