I have the following formula in cell AI2: =IFERROR(INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!K2,'EOL MATRIX'!$B$5:$B$144,0)),INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!M2,'EOL MATRIX'!$B$5:$B$144,0)))
It searches column K for a match and if one is not found, it then searches column M. All is fine, until any of the columns are resorted, and then the row numbers are skewed. The formula in cell AI2 changes to:
=IFERROR(INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!K6167,'EOL MATRIX'!$B$5:$B$144,0)),INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!M6167,'EOL MATRIX'!$B$5:$B$144,0)))
I have tried using absolute references, partial absolute, etc.
Can anyone tell me where I am going wrong?
TIA
It searches column K for a match and if one is not found, it then searches column M. All is fine, until any of the columns are resorted, and then the row numbers are skewed. The formula in cell AI2 changes to:
=IFERROR(INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!K6167,'EOL MATRIX'!$B$5:$B$144,0)),INDEX('EOL MATRIX'!$C$5:$C$144,MATCH('ALL SITES (3)'!M6167,'EOL MATRIX'!$B$5:$B$144,0)))
I have tried using absolute references, partial absolute, etc.
Can anyone tell me where I am going wrong?
TIA
