New Contributor

# getting #SPILL! error

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

4 Replies

# Re: getting #SPILL! error

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))&"","")``

# Re: getting #SPILL! error

Thank you, Detlef Lewin! Definite simpler and tidier approach

# Re: getting #SPILL! error

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

# Re: getting #SPILL! error

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