VLOOKUP formula results unexpectedly change from correct values to #REF!

[au2010]

I have two files, call them FileA.xlsx and FileB.xlsx.

FileA contains VLOOKUP formulas that refer to a range in FileB.

When both files are open, the VLOOKUP formulas work perfectly, returning exactly the expected results.

But when FileB is closed, the VLOOKUP formulas in FileA return a #REF! error.

If FileB is then opened, the VLOOKUP formulas immediately work perfectly again.

Does anyone know why this would occur? Any idea on how to trouble-shoot it or what settings that might be affecting this?

I am using Excel 2016.

Thank you.

 

[Fluff]

@Fluff, I don't think that the typo that you've identified would result in an error only when the source workbook is closed.

Agreed.

 

[au2010]

@Fluff: you are correct about the typo. The range for the MATCH portion is a1:z1, not a1:a1000. But that was just a typo in my post. In my actual Excel files, the formula is correct and the range is a1:z1.

@jasonb75: There are no INDIRECT references. INDIRECT is not used anywhere in either FileA or FileB.

Thanks.

 

[Fluff]

In that case I don't know what is happening unfortunately & I cannot replicate that behaviour.

 

[au2010]

@Fluff: Understood. Thank you for taking a look at it.

 

[Fluff]

You're welcome, Sorry I couldn't be of more help.

 

[jasonb75]

Have you tried evaluating the formula to see where the error first occurs?

If you can identify which range used in the formula is causing the error then it might help in isolating the problem.