Jul 25 2020 11:10 AM
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!
Jul 25 2020 11:28 AM
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))&"","")
Jul 25 2020 04:12 PM
Jul 25 2020 04:47 PM
@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,""))
Jul 26 2020 11:19 AM
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