VLOOKUP function returns unexpected result

Aspose.Cells Product Family
, ,
1

VLOOKUP function behaves differently in Aspose.Cells and results don’t match with Excel.

In below example, Excel will return #REF! whereas Aspose will return #N/A:
=VLOOKUP(1/1/2020,$C1:$D4,#N/A,FALSE)

If value is not found both Excel and Aspose will return #N/A. However, if value is found but column index is not in range then Excel returns #REF! whereas Aspose returns #N/A error.

Attached is the Excel file in which I have reproduced this error. In this workbook if you look at A1 cell it returns #REF! in Excel. However, if we calculate/save the same workbook in Aspose, A1 will return #N/A.

Please look into this issue and let me know if you need anything else from my side.

vlookup.zip (16.1 KB)

2

@sajad.deyargaroo
We can reproduce the issue by testing on the latest version v25.3 using sample files and the following sample code. VLOOKUP function returns unexpected result after calling the formula calculation.

Workbook excel = new Workbook(filePath + "TEXT_ERROR.xlsx");
Worksheet sheet = excel.Worksheets[0];
excel.RefreshDynamicArrayFormulas(true);
excel.CalculateFormula();
Console.WriteLine(sheet.Cells["A1"].StringValue);

The output:

#N/A

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58134

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.