Forum Discussion

Vicharak's avatar
Vicharak
Copper Contributor
Dec 31, 2022

Vlookup behaving strangly

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..

Regards,
Vishal Jain

Excel
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 31, 2022

    Vicharak 

    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?

     

    • Vicharak's avatar
      Vicharak
      Copper Contributor
      Jan 02, 2023

      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..


      Regards,

       

      Vishal Jain

Share

Resources