Index-Match Formula Issue Across Table

Asked Dec 29 '20 at 20:41

Active Dec 29 '20 at 20:50

Viewed 18 times

Summary

I want to be able to copy over the index-match formula so that it fills in the rest of the table
when I do so now, I get the #ref error message

Have tried

Different combinations of column, row settings within formula but I get the #spill error

Current Formula I'm using

=INDEX($D$2:$D$9994,MATCH($F2,$B$2:$B$9994,0),MATCH(G$1,$C$2:$C$9994,0))

Image of excel sheet

edited Dec 29 '20 at 20:50

Scott Craner

asked Dec 29 '20 at 20:41

Juan Gutierrez

the third criterion of INDEX is the COLUMN and not another ROW criterion. See duplicate on how to do two criteria lookups. – Scott Craner Dec 29 '20 at 20:53
@ScottCraner used the helper column method with index-match and it worked great, thanks! – Juan Gutierrez Dec 29 '20 at 21:35
Why? use the FILTER method: `=@FILTER($D:$D,($B:$B=$F2)*($C:$C=G$1))` – Scott Craner Dec 29 '20 at 21:37
or the SUMIFS: `=SUMIFS($D:$D,$B:$B,$F2,$C:$C,G$1)` – Scott Craner Dec 29 '20 at 21:39

0 Answers0