getting #SPILL! error

%3CLINGO-SUB%20id%3D%22lingo-sub-1545847%22%20slang%3D%22en-US%22%3Egetting%20%23SPILL!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545847%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20copy%20my%20last%20meeting's%20comments%26nbsp%3B%20(Column%20K)%20in%20this%20week's%20meeting%20(Column%20C)%20for%20the%20remaining%20Orders%20IDs.%3C%2FP%3E%3CP%3EI%20came%20up%20with%20this%20formula%20that's%20showing%20%23SPILL%20error.%20What's%20wrong%20with%20it%3F%20Or%20is%20there%20an%20easier%20formula%3F%3C%2FP%3E%3CP%3E%3DIF(SEARCH(B4%3B%24J%244%3A%24J%2411%3B)%3BINDEX(%24J%244%3A%24M%2411%3BIF(AND(MATCH(B4%3B%24J%244%3A%24J%2411%3B0)%3DMATCH(D4%3B%24L%244%3A%24L%2411%3B0)%3BMATCH(D4%3B%24L%244%3A%24L%2411%3B0))%3DMATCH(E4%3B%24M%244%3A%24M%2411%3B0)%3BMATCH(B4%3B%24J%244%3A%24J%2411%3B0)%3B%22Delivered%22)%3B2)%3B%22New%22)%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3BThanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1545847%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545866%22%20slang%3D%22en-US%22%3ERe%3A%20getting%20%23SPILL!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739853%22%20target%3D%22_blank%22%3E%40Alvaro18%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20see%20any%20%23SPILL!%20error.%3C%2FP%3E%3CP%3EBut%20I%20would%20suggest%20this%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(INDEX(K%3AK%2CAGGREGATE(15%2C6%2CROW(%24K%244%3A%24K%2411)%2F(%24J%244%3A%24J%2411%3DB4)%2F(%24L%244%3A%24L%2411%3DD4)%2C1))%26amp%3B%22%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546018%22%20slang%3D%22en-US%22%3ERe%3A%20getting%20%23SPILL!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546018%22%20slang%3D%22en-US%22%3EThank%20you%2C%20Detlef%20Lewin!%20Definite%20simpler%20and%20tidier%20approach%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546031%22%20slang%3D%22en-US%22%3ERe%3A%20getting%20%23SPILL!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739853%22%20target%3D%22_blank%22%3E%40Alvaro18%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20have%20the%20new%20XLOOKUP%20function%2C%20here's%20a%20variant%20using%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DT(XLOOKUP(B4%3AB11%26amp%3BD4%3AD11%2CJ4%3AJ11%26amp%3BL4%3AL11%2CK4%3AK11%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546738%22%20slang%3D%22en-US%22%3ERe%3A%20getting%20%23SPILL!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546738%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739853%22%20target%3D%22_blank%22%3E%40Alvaro18%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNA(MATCH(%24B4%26amp%3B%24D4%26amp%3B%24E4%2C%24J%244%3A%24J%2411%26amp%3B%24L%244%3A%24L%2411%26amp%3B%24M%244%3A%24M%2411%2C0))%2C%22New%22%2C%22Delivered%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAnd%20I'd%20suggest%20to%20apply%20conditional%20formatting%20not%20to%20color%20every%20even%20row%20manually%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20771px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207996i98932EC6B75550AC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I want to copy my last meeting's comments  (Column K) in this week's meeting (Column C) for the remaining Orders IDs.

I came up with this formula that's showing #SPILL error. What's wrong with it? Or is there an easier formula?

=IF(SEARCH(B4;$J$4:$J$11;);INDEX($J$4:$M$11;IF(AND(MATCH(B4;$J$4:$J$11;0)=MATCH(D4;$L$4:$L$11;0);MATCH(D4;$L$4:$L$11;0))=MATCH(E4;$M$4:$M$11;0);MATCH(B4;$J$4:$J$11;0);"Delivered");2);"New")

 Thanks for your help!

4 Replies
Highlighted

@Alvaro18 

I can't see any #SPILL! error.

But I would suggest this formula:

=IFERROR(INDEX(K:K,AGGREGATE(15,6,ROW($K$4:$K$11)/($J$4:$J$11=B4)/($L$4:$L$11=D4),1))&"","")
Highlighted
Thank you, Detlef Lewin! Definite simpler and tidier approach
Highlighted

@Alvaro18 , if you have the new XLOOKUP function, here's a variant using this:

 

=T(XLOOKUP(B4:B11&D4:D11,J4:J11&L4:L11,K4:K11,""))
Highlighted

@Alvaro18 

As variant that could be

=IF(ISNA(MATCH($B4&$D4&$E4,$J$4:$J$11&$L$4:$L$11&$M$4:$M$11,0)),"New","Delivered")

And I'd suggest to apply conditional formatting not to color every even row manually

image.png