SOLVED

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.

 

=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 Replies
best response confirmed by Bert Onstott (New Contributor)
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.

Thanks, that worked!