VLOOKUP not working if both worksheets not open

[Tracey Hartley]

Hi all. I've checked out previous threads and can see that this issue has been raised before, but I've tried the suggested solutions and it makes no difference - hoping someone can help?

=IFERROR(VLOOKUP([@Name],'M:\InspireAll Generic and Admin\Staff Admin\Audit\All Staff Audit _Fam Centre.xlsx'!AllStaff_Table[#All],11,FALSE),"Not Found")

Works beautifully if the All Staff Audit_Fam Centre file is open, but if I close it then make a change in the destination worksheet, it suddenly changes the cell back to "Not Found" - what am I doing wrong?!

 

[Fluff]

I suspect it's because you are looking up a table.
If you change the lookup range to cell references then you should be ok.

 

[Tracey Hartley]

That works and here was me, trying to keep things neat and tidy by referencing a table, rather than a range! Thanks for your prompt response and help!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Tracey Hartley]

Sorry, me again - it stopped working? If I remove the iferror from around the vlookup it works fine, whether or not the other workbook is open, but if I leave the iferror on, then it just returns "not found" for everyone?!
=IFERROR(VLOOKUP([@Name],'M:\InspireAll Generic and Admin\Staff Admin\Audit\[All Staff Audit _Fam Centre.xlsx]All Staff'!$A$1:$AK$179,11,FALSE),"Not Found") - this works sporadically
=VLOOKUP([@Name],'M:\InspireAll Generic and Admin\Staff Admin\Audit\[All Staff Audit _Fam Centre.xlsx]All Staff'!$A$1:$AK$179,11,FALSE) - this seems to work, but I'm frustrated by not being able to highlight those it can't find?!

 

[Fluff]

What if you try

=IFNA(VLOOKUP([@Name],'M:\InspireAll Generic and Admin\Staff Admin\Audit\[All Staff Audit _Fam Centre.xlsx]All Staff'!$A$1:$AK$179,11,FALSE),"Not Found")

 

[Tracey Hartley]

It doesn't update when I reopen and comes up with "Open source to update values"?

 

[Fluff]

In that case, I'm not sure what the problem as it works fine for me.