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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
Does your VLOOKUP state the full path to file B?

Something like
VLOOKUP(lookup value,'C:\data\[FileB.xlsx]Sheet1'!Range,column number,FALSE) of course changing the part in bold to the actual file path.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,544
Office Version
  1. 365
Platform
  1. Windows
What is your vlookup formula?
Does it refer to a structured table?
 

Lian

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
It is not concern of vlookup. There are some fuctions called Volatile functions which would cause errors. It works only the refering file is open.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,544
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Lian welcome to Mrexcel
It is not concern of vlookup.
That is not true, there are situations when a standard vlookup will behave as the OP as stated.
 

Lian

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
@Lian welcome to Mrexcel

That is not true, there are situations when a standard vlookup will behave as the OP as stated.
Hi Fluff, thanks for your reminding and point out this for me.
I think it it much better to check his formula or screen capture.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,544
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think it it much better to check his formula
Which is why I asked the Op to post the formula. ;)
 

au2010

New Member
Joined
Jun 7, 2012
Messages
27
This is the OP. Thank you for the replies. Sorry I was slow to come back on. To address the comment/questions raised above:

1. The vlookup contains the full path and is in the format noted above ("VLOOKUP(lookup value,'C:\data\[FileB.xlsx]Sheet1'!Range,column number,FALSE)" except that "column number" is replaced with a match formula.

So the VLOOKUP in FileA looks like this, where the data is in the range a1:z1000 in FileB:

VLOOKUP(x1,'C:\data\[FileB.xlsx]Sheet1'!A1:Z1000,MATCH(x2,'C:\data\[FileB.xlsx]Sheet1'!A1:a1000,0),FALSE)

where X1 is the Lookup value and X2 is the Match value (the exact path is of course different, but the full and correct path is included in both the VLOOKUP part and the MATCH part).

2. The data in FileB is not a structured table. It is just a regular range of cells, as noted above. Column A contains a list of employee names entered as text. Row 1 contains product names also as text. Other cell values are numbers representing sales totals. So, for example, if "Joe Smith" is the value in Cell A4 and, and "Light "Bulbs" is the the value in Cell D1, then Cell D4 = sales of light bulbs by Joe Smith. The purpose of the VLOOKUP formula is to return the sales figure for any combination of employee name and product.

3. As long as both FileA and FileB are open, the VLOOKUP works perfectly.

4. But if FileB is closed, and the formulas in FileA are recalculated, the VLOOKUP returns #REF

5. If FileB is reopened, and FileA is recalculated again, the #REF goes away and the correct value is returned. This is without any changes being made to the formula.

6. Furthermore, if both files are closed, and only FileA is reopened, the VLOOKUP appears to return the correct value. But as soon as the file is recalculated, the #REF error returns until File B is also reopened.

This makes no sense to me, but perhaps someone with better Excel knowledge can explain it.

Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,544
Office Version
  1. 365
Platform
  1. Windows
You're formula looks wrong, I think it should be
Excel Formula:
VLOOKUP(x1,'C:\data\[FileB.xlsx]Sheet1'!A1:Z1000,MATCH(x2,'C:\data\[FileB.xlsx]Sheet1'!A1:Z1,0),FALSE)
Other than that I can see no reason why that formula would exhibit the problems you are having.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
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.

@au2010 Do any precedent cells (or named range definitions) contain an INDIRECT reference? Precedent would mean cells that are referred to in the formula, if those cells contain formulas then the cells that those formulas refer to, and so on.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,568
Members
416,118
Latest member
kamil_tuncer

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