SOLVED

Filter function in table

%3CLINGO-SUB%20id%3D%22lingo-sub-2858628%22%20slang%3D%22en-US%22%3EFilter%20function%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2858628%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3CBR%20%2F%3EI%20am%20using%20filter%20function%20to%20bring%202%20columns%20from%20a%20another%20table.%20I%20want%20to%20make%20it%20a%20table.%20But%20I%20get%20the%20regular%20%23SPILL%20error%20message.%20Is%20it%20a%20limitation%20or%20is%20there%20a%20way%20to%20write%20filter%20formula%20for%20a%20table.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3EImalka%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2858628%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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%3CLINGO-SUB%20id%3D%22lingo-sub-2877681%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20function%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2877681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132817%22%20target%3D%22_blank%22%3E%40ImalkaJ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESpills%20are%20not%20married%20with%20tables%2C%20you%20may%20use%20dynamic%20array%20function%20within%20table%20if%20only%20it%20returns%20one%20value%2C%20not%20spill.%20That's%20by%20design%2C%20at%20least%20by%20current%20design.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2878854%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20function%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132817%22%20target%3D%22_blank%22%3E%40ImalkaJ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Espill%20does%20not%20work%20inside%20excel%20defined%20tables%2C%20it%20has%20to%20be%20a%20regular%20range%20otherwise%20you're%20just%20going%20to%20get%20a%20%23spill%20error%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi there,
I am using filter function to bring 2 columns from a another table. I want to make it a table. But I get the regular #SPILL error message. Is it a limitation or is there a way to write filter formula for a table.

Thanks,
Imalka 

2 Replies
best response confirmed by ImalkaJ (Occasional Contributor)
Solution

@ImalkaJ 

Spills are not married with tables, you may use dynamic array function within table if only it returns one value, not spill. That's by design, at least by current design.

@ImalkaJ 

 

spill does not work inside excel defined tables, it has to be a regular range otherwise you're just going to get a #spill error