SOLVED

XLookup with Structured Table References

%3CLINGO-SUB%20id%3D%22lingo-sub-1575369%22%20slang%3D%22en-US%22%3ERe%3A%20XLookup%20with%20Structured%20Table%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F212575%22%20target%3D%22_blank%22%3E%40Bert%20Onstott%3C%2FA%3E%26nbsp%3B%20If%20those%20are%20real%20names%20and%20info%2C%20please%20delete%20and%20only%20supply%20phony%20informations.%3C%2FP%3E%3CP%3EAs%20for%20what%20is%20happening%20is%20the%20'%40'%20which%20is%20saying%20look%20only%20at%20this%20specific%20row.%26nbsp%3B%20So%20change%20it%20to%20this%20and%20it%20seems%20to%20work%20fine%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%5B%40Name%5D%2CMasterlistTable%5BName%5D%2CMasterlistTable%5BPhone%201%5D%2C%22error%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eso%20now%20it%20looks%20at%20the%20Name%20in%20this%20table%20on%20THIS%20(%40)%20line%2C%20but%20searches%20the%20entire%20MasterlistTable%5BName%5D%20instead%20of%20only%201%20row%20of%20that%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582342%22%20slang%3D%22en-US%22%3ERe%3A%20XLookup%20with%20Structured%20Table%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582342%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20that%20worked!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575331%22%20slang%3D%22en-US%22%3EXLookup%20with%20Structured%20Table%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575331%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20find%20anything%20that%20directly%20speaks%20to%20this%2C%20so%20I'll%20post%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20of%20individuals%20with%20some%20columns%20of%20information.%26nbsp%3B%20It's%20defined%20as%20as%20a%20table.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20another%20table%20where%20I%20would%20like%20to%20use%20XLOOKUP%20to%20search%20that%20table%20and%20return%20some%20information%20from%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20in%20the%20second%20table%20have%20references%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DXLOOKUP(%5B%40Name%5D%2CMasterlistTable%5B%40Name%5D%2CMasterlistTable%5B%40%5BPhone%201%5D%5D%2C%22error%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThey%20all%20return%20a%20%22value%20not%20available%22%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20copied%20that%20sheet%20to%20another%20and%20changed%20the%20table%20references%20to%20use%20named%20ranges%20instead.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DXLOOKUP(%24A2%2CName%2CPhone1%2C%22error%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThose%20work%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20shed%20light%20on%20what's%20going%20on%20here%3F%26nbsp%3B%20I%20uploaded%20the%20simple%20workbook%20with%20the%20two%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20love%20to%20use%20xlookup%20to%20reference%20the%20data%20in%20the%20table%20and%20take%20advantage%20of%20its%20simplicity%20and%20power.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1575331%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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!