XLookup with Structured Table References

[Bert Onstott]

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.

[#2 · Accepted solution]

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

Thanks, that worked!

[#4]

Thank you so much!!! I am new to using XLOOKUP and did not know about this @ symbol for table structure. This saved me!!