pjfanning opened a new pull request, #1299: URL: https://github.com/apache/poi/pull/1299
Fixes https://bz.apache.org/bugzilla/show_bug.cgi?id=70145 ## Problem The common "multiple criteria" idiom ``` =XLOOKUP(1,(B2:B11=G2)*(C2:C11=G3),D2:D11) ``` returned `#VALUE!` in POI where Excel returns the matching row's value. Whether an operator with an area operand is evaluated element-wise (array mode) is decided in `WorkbookEvaluator` by looking ahead for the function that will consume its result and checking it for the `ArrayMode` marker. That lookahead only recognised built-in functions via `FunctionEval.getBasicFunction`. `XLOOKUP` is a "future function" invoked through the external-function `FuncVarPtg`, so the comparisons were reduced to the single value for the formula's row and `XLOOKUP` received a scalar `0`/`1` for `lookup_array`, which `LookupUtils.resolveTableArrayArg` rejects. ## Fix - `WorkbookEvaluator`: replace the "first `FuncVarPtg` that follows" lookahead with a walk over the remaining RPN tokens that tracks the position of the operator's result on the evaluation stack. It therefore finds the operation that actually consumes the result (nested calls such as `SUMPRODUCT((A1:A3=1)*IF(...))` no longer point at the wrong function) and, for an external function, resolves the user-defined function from the `FunctionNameEval` already on the stack. - `XLookupFunction` implements `ArrayMode`. - `ArrayMode` javadoc notes that `FreeRefFunction`s may implement it. ## Tests - `TestXLookupFunction.testBug70145ArrayExpressionLookupArray` (HSSF) and `TestXSSFXLookupFunction.testBug70145ArrayExpressionLookupArray` (XSSF, `_xlfn.XLOOKUP` as the reporter wrote it) reproduce the bug report. - `:poi` formula, HSSF usermodel/record suites and `:poi-ooxml` formula/XSSF suites pass. `TestXSSFBugs.stackoverflow23114397` and `TestSXSSFSheetAutoSizeColumn` fail on my machine on unmodified trunk too (font-metric dependent) and are unrelated. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
