Vlookup behaving strangly

New Contributor

Hi All,

From last few Months, Vlookup function linked to one particular XLSB file is behaving weirdly.. The XLSB File is created from file which was working fine earlier.. If the XLSB file from which Vlookup is pulling values is closed, then Vlookup given #N/A Error.. As soon as we open the XLSB file, Vlookup gives proper results.. Strangely when file is closed, #N/A error is only for few rows, whereas other rows values is correctly pulled from same file (even though the XLSB File is closed) In other words, for e.g. same vlookup function used in 10 rows. If Work book is closed, 5 Rows gives #N/A error, 5 Rows gives proper Values.. As soon as we open file, all 10 rows shows proper value..
If anyone has come across similar issues and got any resolution, please help..

Vishal Jain

2 Replies


INDIRECT() and COUNTIF() and its cousins are known not to work with closed workbooks.

Are you sure it is the VLOOKUP() that is causing the problem?


@Detlef Lewin 

Thanks Lewin..

I am sure it is Vlookup.. Just FYI, Vlookup is nested in IFERROR, which is further nested in ROUND.

Also the same formula was working 7-8 months ago.. Facing this issue from last 7-8 months.. Was ignoring it so far.. but it taking much of my bandwidth so thought of checking if any resolutions is available for this issue or is there any issue with my setup / config..



Vishal Jain