[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-08-07
Medium Priority
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
  • 2
LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 40245823
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

ID: 40245873
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

ID: 40245876
MATCH attribute combined with potential network lag

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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