+ Reply to Thread
Results 1 to 2 of 2

Index formula #N/A when changing input data

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    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.

    Thank you in advance for your help.

    Best regards //John

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,141

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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