#SPILL!

%3CLINGO-SUB%20id%3D%22lingo-sub-1871060%22%20slang%3D%22en-US%22%3E%23SPILL!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871060%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3DIFERROR(IF(MATCH(%5B%40VPPID%5D%2CMainDB.xlsx!Main%5BVPPID%5D%2C0)%2CMainDB.xlsx!Main%5BAPPID%5D%2C%22%22)%2C%22%22)%3C%2FP%3E%3CP%3Eand%20I'm%20getting%20a%20%23SPILL!%20error.%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1871060%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%3CLINGO-SUB%20id%3D%22lingo-sub-1871442%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871442%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636766%22%20target%3D%22_blank%22%3E%40Pianoman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20because%20if%20the%20logical%20condition%20is%20True%2C%20the%20formula%20will%20return%20all%20the%20values%20from%20a%20column%20of%20a%20table%20(MainDB.xlsx!Main%5BAPPID%5D)%20not%20a%20single%20or%20scalar%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1872257%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1872257%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636766%22%20target%3D%22_blank%22%3E%40Pianoman%3C%2FA%3E%26nbsp%3B%20Without%20more%20info%20this%20is%20just%20a%20guess%20at%20what%20you%20want%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(MainDB.xlsx!Main%5BAPPID%5D%2CMATCH(%5B%40VPPID%5D%2CMainDB.xlsx!Main%5BVPPID%5D%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877063%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThanks.%20I%20did%20work%20it%20out%20in%20the%20end.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877065%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThanks.%20I%20did%20work%20it%20out%20in%20the%20end.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm using the following formula:

=IFERROR(IF(MATCH([@VPPID],MainDB.xlsx!Main[VPPID],0),MainDB.xlsx!Main[APPID],""),"")

and I'm getting a #SPILL! error.

Can anyone help?

5 Replies

@Pianoman 

That's because if the logical condition is evaluated as True, the formula will return all the values from a column of a table (MainDB.xlsx!Main[APPID]) not a single or scalar value.

@Pianoman  Without more info this is just a guess at what you want:

=IFERROR(INDEX(MainDB.xlsx!Main[APPID],MATCH([@VPPID],MainDB.xlsx!Main[VPPID],0)),"")

@Subodh_Tiwari_sktneer Thanks. I did work it out in the end.

@mtarler Thanks. I did work it out in the end. 

You're welcome @Pianoman!