Issue filtering rows containing INDEX MATCH

[sshea]

I have ~2,000 rows of information in columns A-M in worksheet "2014_ALL".
Column M contains an INDEX MATCH formula which pulls information from another worksheet, "REPORT".
My formula works perfectly, however I need to be able to filter and sort the information. When I attempt to sort the information, the formula does not update properly and returns #N/A. The problem seems to come from the last cell reference in the formula.


ORIGINAL FORMULA IN CELL M10
=INDEX(REPORT!A$2:F$2500,MATCH(1,(REPORT!B$2:B$2500=B10)*(REPORT!D$2:D$2500='2014_All'!E10),0),6)
FORMULA IN CELL M10 AFTER SORTING BY COLUMN C
=INDEX(REPORT!A$2:F$2500,MATCH(1,(REPORT!B$2:B$2500=B10)*(REPORT!D$2:D$2500='2014_All'!E288),0),6)

Any ideas how to rectify this??

 

[Capt.Ragnar]

Turn your filtered data into a table.

your E10 and E288 in Match function will then need to be changed to... table1[@"ColumnName"]... This will allow you to still filter the table but not lose relative references in the formula.