# Index formula #N/A when changing input data

1. ## Index formula #N/A when changing input data

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.

Best regards //John

2. ## Re: Index formula #N/A when changing input data

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1