Index formula #N/A when changing input data

[John Karlsson]

Hi all,

I have incurred a problem and thought i would try to ask for your help.

Case: I have made a INDEX with MATCH formula in one tab looking for certain amounts in a second tab. Formula is working fine and i have only used absolute references (except for the lookup values). However, when i change (i.e., deleting all data in tab 2 and inserting new data) the formula replies #N/A and some values in the formula have changed. Is there anyway I can stop this from happening, it only takes a couple of minutes to make the formula again but it would be nice not to. Below you can see the formula I'm using.

=INDEX('3.3 UN, per 2'!$A$1:$CA$300;MATCH($A3;'3.3 UN, per 2'!$A$1:$A$300;0);MATCH(L$1;'3.3 UN, per 2'!$A$1:$CA$1;0))

And in smaller sequences it is:

INDEX('3.3 UN, per 2'!$A$1:$CA$300;

MATCH($A3;'3.3 UN, per 2'!$A$1:$A$300;0);

MATCH(L$1;'3.3 UN, per 2'!$A$1:$CA$1;0))

So, when changing the input in tab "3.3 UN, per 2" I get the error shown above.

Thank you in advance for your help.

Best regards //John

[Pete_UK]

Instead of deleting the cells containing the data, you should just delete the contents, and then use Paste Special when copying the new data in, instead of a straight Paste.

You can also change your formula to this:

=IFERROR(INDEX('3.3 UN, per 2'!$A$1:$CA$300;MATCH($A3;'3.3 UN, per 2'!$A$1:$A$300;0);MATCH(L$1;'3.3 UN, per 2'!$A$1:$CA$1;0)),"")

(changes shown in red) to hide the error.

Hope this helps.

Pete