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.