vlookup #REF error only when the returned value is a blank

[jdubyaIII]

First time posting, thank you in advance for your responses. I have a vlookup to a separate workbook that works perfectly when the separate workbook is open. When the separate workbook is closed, I get #REF ! errors but only when the referenced cell is blank. Any idea what might be causing this, and how to fix it?

 

[t0azt]

Wrap it in an IFERROR

IFERROR(your formula,"")

 

[jdubyaIII]

thanks, that did the trick!

 

[Aladin Akyurek]

What is the formula that leads to #REF !? Normally, a VLOOKUP formula would work with cloded books...

 

[jdubyaIII]

What is the formula that leads to #REF !? Normally, a VLOOKUP formula would work with cloded books...

I thought so too. Here it is:

=IF(ISNA(VLOOKUP(A27,'P:\[inventory.xls]Sheet1'!$A$1:$C$65536,3,FALSE)),"Sold",IF(VLOOKUP(A27,'P:\[inventory.xls]Sheet1'!$A$1:$C$65536,3,FALSE)=0,"",VLOOKUP(A27,'P:\[inventory.xls]Sheet1'!$A$1:$C$65536,3,FALSE)))

what's strange to me is that it only fails if the cell on the lookup workbook is blank. I have some macros on this worksheet, could that have something to do with it?

 

[Aladin Akyurek]

Try to inspect the result cell when you get the #REF ! in order to see what you really have in that cell.