Index Match formula not working when cut and paste into a different sheet

[dsmith1088]

Hi there

I have a cell which contains the following formula:

=INDEX(C7:L46,MATCH(1,(A7:A1048576=O1)*(B7:B1048576=MROUND(O3,0.1)),0),INDEX(4:4,MATCH(MROUND(O2,25),6:6,0)))

I need to cut and paste this cell into Sheet2 in the same workbook. The formula references automatically update as expected:

=INDEX('MPG Matrix'!C7:L46,MATCH(1,('MPG Matrix'!A7:A1048576='MPG Matrix'!O1)*('MPG Matrix'!B7:B1048576=MROUND('MPG Matrix'!O3,0.1)),0),INDEX('MPG Matrix'!4:4,MATCH(MROUND('MPG Matrix'!O2,25),$G8:$G8,0)))

So the two formulas look exactly the same to me? However, the second formula no longer works, despite all the cell references remaining the same.

Any help would be great, thank you.

 

[MrKowz]

Not all references are exactly the same:

=INDEX(C7:L46,MATCH(1,(A7:A1048576=O1)*(B7:B1048576=MROUND(O3,0.1)),0),INDEX(4:4,MATCH(MROUND(O2,25),6:6,0)))

=INDEX('MPG Matrix'!C7:L46,MATCH(1,('MPG Matrix'!A7:A1048576='MPG Matrix'!O1)*('MPG Matrix'!B7:B1048576=MROUND('MPG Matrix'!O3,0.1)),0),INDEX('MPG Matrix'!4:4,MATCH(MROUND('MPG Matrix'!O2,25),$G8:$G8,0)))

Also, be sure you're committing the formula using CTRL+SHIFT+ENTER to store it as an array formula.

 

[dsmith1088]

Thank you very much. Absolutely no idea why this would happen. I've retried cutting & pasting and it continues to happen! Strange... I've manually updated the formula now.