Lookup Bug

Aspose.Cells Product Family
1

Please see AsposeLookupIssue.7z in https://spideroak.com/browse/share/arcticpenguin/aspose/aspose-bug/

The sheet “Sheet with Error” row 3 show a calculation result that is different between Aspose and Excel. Looks like an issue with the LOOKUP function.

2

@andyczerwonka

Thanks for using Aspose APIs.

We have looked into this issue with the following sample code and found the issue. We have logged the issue in our database for investigation and for a fix. Once, there is some news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42381 - Workbook Calculation is wrong for Lookup Excel formula

Java

Workbook wb = new Workbook(dirPath + "aspose-lookup-bug.xlsm");

Worksheet ws = wb.getWorksheets().get("Sheet with Error");

Cell cell = ws.getCells().get("B3");

System.out.println(cell.getFormula());
System.out.println("B3 Value: " + cell.getStringValue());

wb.calculateFormula();

System.out.println("B3 Value: " + cell.getStringValue());

Console Output

=IF(ISERROR(LOOKUP('Sheet with Error'!B$2,B!$1:$1,B!8:8+B!7:7)),0,LOOKUP('Sheet with Error'!B$2,B!$1:$1,B!8:8+B!7:7))
B3 Value: 117.38
B3 Value:
3

@andyczerwonka,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42381”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

@andyczerwonka

Please download and try the following fix and let us know your feedback.

5

The issues you have found earlier (filed as CELLSJAVA-42381) have been fixed in Aspose.Cells for Java 17.9.