XLookup with Structured Table References

New Contributor

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.




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.




2 Replies
best response confirmed by Bert Onstott (New Contributor)

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

Thanks, that worked!