Use LOOKUP() function using Aspose.Cells for Java

[mm3]

Consider the following formula:

=LOOKUP(2, {“a”,1,2,3}, {“x”,“one”,“two”,“three”})

In Excel 2013, this returns “two”. In Aspose Cells for Java 8.3.2, it returns “N/A”.

The Excel documentation is wishy-washy on what the correct result should be:

Important The values in array must be placed in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

This tells us that it is inadvisable for our look-up table to have “a” before the numeric values 1, 2, 3. The Cells result is probably superior to Excel’s, but unfortunately it can mean that a user’s spreadsheet can produce different results.

This is obviously a corner case, and a spreadsheet that depends upon a well-defined result in such circumstances is asking for trouble. I report this not so much for a fix, but rather to potentially save another user from a long debugging session.

[babar.raza]

Hi Mark,

Thank you for contacting Aspose support.

In reference to Aspose.Cells, if result to any formula is “N/A” that means API hasn’t calculated the value yet. You need to call Workbook.CalculateFormula method to calculate all formulas. In case the results do not change even after the aforesaid call then it is a probably caused due to some bug in the API.

I have checked this scenario with last major release, and was not able to see the problem. Aspose.Cells for Java (Latest Version) calculates the value of formula =LOOKUP(2, {“a”,1,2,3}, {“x”,“one”,“two”,“three”}) as two by creating the Workbook from scratch as well as loading it from disk. Please give the latest version a try on your side and let us know of your feedback.

Java

Workbook book = new Workbook(“D:/book1.xlsx”);
//book.getWorksheets().get(0).getCells().get(“A1”).setFormula("=LOOKUP(2, {“a”,1,2,3}, {“x”,“one”,“two”,“three”})");
book.calculateFormula();
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getStringValue());
book.save(“D:/output.xlsx”);

[mm3]

Hi, Babar:

I can confirm that the reported problem no longer occurs in build 8.5.1.

Thanks for your (very!) quick response.

Mark

[shakeel.faiz]

Hi Mark,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is sorted out now. Let us know if you encounter any other issue, we will be glad to look into it and help you further.