VLOOKUP Works Intermittently

Apr 20, 2016 at 5:24 PM
Edited Apr 20, 2016 at 5:54 PM
This was the original question, although I figured out that this wasn't the case. I'm leaving it for history's sake, but you can skip reading this first part until the edit
Has anybody noticed certain functions, particularly VLOOKUP only work some times? Last night I was testing two separate workbooks that are nearly identical. I opened them with EPPlus, wrote some data, calculated a sheet, and extracted values from that sheet. The first workbook was working perfectly fine while the second workbook was getting a calculation error. I decided to go to sleep and see if I could figure anything out this morning, and now suddenly the first workbook is getting the same calculation error!

If instead of trying to calculate the data and extract it through EPPlus I simply write it to a file, I can open the file and see all the formulas working perfectly.

Has anybody noticed this before? It's very difficult for me to reproduce or give an example since one day a sheet works and the next day it doesn't.

EDIT
So, I'm not sure if somehow I swapped out the workbooks last night, which may have led to my first workbook not working this morning, but I think that's the case.

However, I did finally figure out what the issue is. I had a VLOOKUP with a MATCH inside. Both the VLOOKUP and MATCH functions refer to a different sheet. My formula was written something like:
=VLOOKUP(A35, OtherSheet!$A$1:OtherSheet!$C$42, MATCH($D$1, OtherSheet!$A$1:OtherSheet!$C$1, 0), FALSE)
which works perfectly fine in Excel, but had calculation exception in EPPlus. When I changed the formulas to omit the 2nd reference to OtherSheet it worked in EPPlus:
=VLOOKUP(A35, OtherSheet!$A$1:$C$42, MATCH($D$1, OtherSheet!$A$1:$C$1, 0), FALSE)
Developer
Apr 27, 2016 at 6:32 PM
Hi,

could you create an issue with the same description and attach your workbooks so we can recreate the error.

Alternatively you can try to see if you can find anything using the logger of the formula parser.
workbook.ParserManager.AttachLogger(new FileInfo("c:\\mylogfile.txt"));
and then run the calculate() method again.
Apr 27, 2016 at 9:37 PM
Edited Apr 27, 2016 at 9:58 PM
I have added the issue here https://epplus.codeplex.com/workitem/15455

I used Workbook.FormulaParserManager and it gave the following:
=================================
Timestamp: 4/27/2016 2:56:04 PM

Worksheet: Sheet1
Address: C3
OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE!
   at OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj)
   at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.LookupArguments..ctor(IEnumerable`1 arguments, ArgumentParsers argumentParsers)
   at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.VLookup.Execute(IEnumerable`1 arguments, ParsingContext context)
   at OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()

=================================
Timestamp: 4/27/2016 2:56:04 PM

Worksheet: Sheet1
Address: C7
OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE!
   at OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj)
   at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.LookupArguments..ctor(IEnumerable`1 arguments, ArgumentParsers argumentParsers)
   at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.VLookup.Execute(IEnumerable`1 arguments, ParsingContext context)
   at OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()