INDEX/MATCH & External Data Links results failure (Advanced users only)

Posted on 2014-08-07
Last Modified: 2014-08-07

I have come across a unique problem which has caused a challenging issue for us as we have had some wrong data presented to screen (and only discovered post review). Out of all the formula's on our worksheet (which are all inherently the same and look up the same source data), some formula's have worked and some haven't. What baffles me is that the incorrect returning result formula's are now operational and returning correct results today yet they have been untouched since yesterday. I have checked the source data and there are no issues. I am running Excel 2010.

As this is work related document I am unfortunately unable to post the workbook.

The written formula is;
=IF(ISNA(INDEX('\\network_path\[source_file.xlsx]Source Data'!$O$350:$O$410,MATCH($A1,'\\network_path\[source_file.xlsx]Source Data'!$A$350:$A$410,FALSE))),"No data",INDEX('\\network_path\[source_file.xlsx]Source Data'!$O$350:$O$410,MATCH($A1,'\\network_path\[source_file.xlsx]Source Data'!$A$350:$A$410,FALSE)))

Open in new window

A couple of formula's in this worksheet did return correct results. After some investigation the results that were presented to us for the 2 discovered culprit cells were;
Cell #1 - Result xyz 3 rows below the target cell
Cell #2 - Result xyz located in cell C143

Clearly it has completely failed.

Our method of updating these values is;
#1 - Saving the source_file.xlsx
#2 - Separately updating the data values (via the Data -> Edit Links option)

Could this be a memory leak? Could this be that the 'FALSE' attribute in the match function for these formula's has failed? Could it be that others may have failed?

Would appreciate any insight.
Question by:just4kix
    LVL 24

    Accepted Solution

    OK, firstly I would simplify the formula as such:
    =IFERROR(INDEX('\\network_path\[source_file.xlsx]Source Data'!$O$350:$O$410,MATCH($A1,'\\network_path\[source_file.xlsx]Source Data'!$A$350:$A$410,0)),"No data")

    Open in new window

    Dropping the IF and ISNA will make life easier.

    The issue you are having may be with using FALSE

    MATCH uses 1 , 0 and -1
    By using the word FALSE this may be reading -1 in place of zero.
    Though this may not be the case, it is better to use 0.
    Check help on function MATCH for detail of using 0 in place of FALSE.

    I doubt it is memory leak causing the issue.
    It may be that the file save has not been completed.
    Does the network location have replication?

    Was the data sorted correctly before the save?
    Did the fixed range expand / contract?


    Author Comment

    Thanks Steve. Interestingly enough, '0' was there originally and Excel seems to have replaced every '0' with a 'FALSE'. I did think this may have been causing an issue. I will now insert numerical values, again.

    Regarding the network comments - I think you're onto something as this combined with the above may be the root cause of the problem. The time between file save and when we refresh the links is usually tight. Programmatically, Excel takes a read-only source file for link refreshing. That said, one may question how valid Excel's status notification of 'OK' in the Data Links window actually is for data accuracy.

    Replication also does occur on the network which also may add complexity with the given timeframe.

    We've now done away with the external source and brought the worksheets into the same file. Fingers crossed...

    Author Closing Comment

    MATCH attribute combined with potential network lag

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now