getting #SPILL! error

Copper 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

@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))&"","")
Thank you, Detlef Lewin! Definite simpler and tidier approach

@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,""))

@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