Can't use XLOOKUP/VLOOKUP in a table in MS Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3497202%22%20slang%3D%22en-US%22%3ECan't%20use%20XLOOKUP%2FVLOOKUP%20in%20a%20table%20in%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3497202%22%20slang%3D%22en-US%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(A2%2C%20INDEX7%5BSYMBOL%5D%2C%20INDEX7%5B%5BINSTRUMENT%5D%3A%5B2013%25%5D%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20formula.%20INDEX7%20is%20the%20name%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20fine%20in%20a%20range.%20But%20when%20I%20convert%20the%20range%20to%20a%20table%2C%20it%20stops%20working%20(even%20though%20it%20fits%20the%20row).%20And%20I%20get%20a%20%22%23SPILL!%22%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20what%20I%20can%20do%20alternatively%3F%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-3497202%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3497216%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20use%20XLOOKUP%2FVLOOKUP%20in%20a%20table%20in%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3497216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1421613%22%20target%3D%22_blank%22%3E%40VarunAgw%3C%2FA%3E%26nbsp%3BIndeed%2C%20you%20can%20not%20use%20dynamic%20array%20formulas%20inside%20a%20structured%20table.%20You%20need%20to%20write%20the%20formulas%20separately.%20One%20for%20each%20column%20or%2C%20as%20you%20already%20have%20discovered%2C%26nbsp%3B%20work%20in%20a%20regular%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3497305%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20use%20XLOOKUP%2FVLOOKUP%20in%20a%20table%20in%20MS%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3497305%22%20slang%3D%22en-US%22%3EDidn't%20realize%20that%20it%20allows%20individual%20formula%20for%20each%20cell%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
=XLOOKUP(A2, INDEX7[SYMBOL], INDEX7[[INSTRUMENT]:[2013%]])

 

This is my formula. INDEX7 is the name of the table.

 

This works fine in a range. But when I convert the range to a table, it stops working (even though it fits the row). And I get a "#SPILL!" error.

 

Any idea what I can do alternatively?

 

 

2 Replies

@VarunAgw Indeed, you can not use dynamic array formulas inside a structured table. You need to write the formulas separately. One for each column or, as you already have discovered,  work in a regular range.

Didn't realize that it allows individual formula for each cell