... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2015-08-17

LOOKUP vs. Aspose Cells for Java

Consider the following Excel 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 reported this to Aspose not so much for a fix, but rather to potentially save another user from a long debugging session.

Blog Archive