https://bz.apache.org/bugzilla/show_bug.cgi?id=70145
Bug ID: 70145
Summary: XLOOKUP returns #VALUE! when lookup_array is an array
expression such as `(B2:B11=G2)*(C2:C11=G3)`
Product: POI
Version: 5.5.1-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: SS Common
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Created attachment 40197
--> https://bz.apache.org/bugzilla/attachment.cgi?id=40197&action=edit
java class reproducing the issue
XLOOKUP is commonly used with multiple match conditions by passing a
multiplied array of boolean comparisons as the `lookup_array` argument and
searching for `1`, e.g.:
```
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)
```
Excel evaluates each comparison as an array over its range, multiplies them
element-wise to produce a column of 0/1 values, and XLOOKUP returns the row
where the product equals the lookup value `1`. I've found many tutorials
recommending this approach, but nothing in Microsoft's documentation. The
formula also errors in Google Sheets.
Apache POI's formula evaluator returns **#VALUE!** for this formula instead of
the value Excel computes.
The attached `XlookupArrayReproducer.java` builds the workbook in memory and
evaluates the formula in G4, which returns #VALUE. In Excel, it evaluates to 1.
I traced the error to XLookupFunction.java, line 107. This calls
LookupUtils.resolveTableArrayArg() on the lookup_array arg.
resolveTableArrayArg() throws if the arg is not a range. I don't follow it
completely, but it seems like the preceding evaluator code reduces the arg in a
single-value context, and by the time it gets to XLookupFunction, it's a scalar
(0 or 1).
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
