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

au2010

New Member
Joined
Jun 7, 2012
Messages
27
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,681
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

au2010

New Member
Joined
Jun 7, 2012
Messages
27
@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,681
Office Version
  1. 365
Platform
  1. Windows
In that case I don't know what is happening unfortunately & I cannot replicate that behaviour.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,681
Office Version
  1. 365
Platform
  1. Windows
You're welcome, Sorry I couldn't be of more help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,230
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top