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

Hi,

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.
just4kixAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
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?

ATB
Steve.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
just4kixAuthor Commented:
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...
0
just4kixAuthor Commented:
MATCH attribute combined with potential network lag
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.