我最近刚踩过这个坑！用POI 5.2.5处理带多列拼接的XLOOKUP时确实会遇到#VALUE!错误，折腾了好几天才摸清楚几个可能的原因和解决办法，分享给你试试：

1. POI对动态数组拼接的支持不足 #

你公式里用&直接拼接多列区域（比如Sheet2!$A$4:$A$600&Sheet2!$C$4:$C$600&Sheet2!$E$4:$E$600），这属于Excel的动态数组运算，但POI 5.2.5的XLOOKUP实现对这种动态生成的数组支持还不完善——毕竟POI对Excel新功能的跟进总是慢半拍。

解决办法：用辅助列替代动态拼接
最稳妥的方式是先在Sheet2里把需要拼接的列提前合并成一个辅助列，比如在Z4单元格输入公式：

=CONCAT(A4, C4, E4)

然后下拉填充到Z600，之后你的XLOOKUP公式就可以改成针对单一列的查询：

cell.setCellFormula("_xlfn.XLOOKUP(CONCAT(LEFT(C$18,11), C$9, C$11), Sheet2!$Z$4:$Z$600, Sheet2!$N$4:$N$600, \"Not found\")");

这样POI的FormulaEvaluator就能正常解析，因为查找范围变成了普通的单元格区域，不是动态生成的数组。

2. 单独验证拼接部分是否正常 #

你可以先拆分公式，单独验证LEFT(C$18,11)&C$9&C$11这个查找值是否能被正确计算：

Cell testCell = workbook.getSheetAt(0).createRow(0).createCell(0);
testCell.setCellFormula("LEFT(C$18,11)&C$9&C$11");
CellValue testResult = evaluator.evaluate(testCell);
if (testResult.getCellType() == CellType.ERROR) {
    // 说明LEFT函数或字符串拼接本身就有问题，检查单元格C18、C9、C11的内容是否符合预期
}

如果这部分返回错误，那问题可能出在LEFT函数的参数或者单元格内容上，比如C18的文本长度不足11位？

3. 尝试升级POI版本 #

POI的每个版本都会修复大量公式解析的bug，5.2.5之后的版本（比如5.2.6、5.2.7）可能已经修复了XLOOKUP处理动态数组的问题。你可以试试升级依赖，说不定直接就能解决问题。

4. 检查默认值的转义是否正确 #

虽然你代码里的\"Not found\"看起来转义是对的，但有时候POI对公式中的字符串处理会有小问题。你可以试试把默认值换成单元格引用，比如在Sheet1的某个空白单元格（比如D1）输入“Not found”，然后公式改成：

cell.setCellFormula("_xlfn.XLOOKUP(LEFT(C$18,11)&C$9&C$11,Sheet2!$A$4:$A$600&Sheet2!$C$4:$C$600&Sheet2!$E$4:$E$600,Sheet2!$N$4:$N$600,D1)");

排除字符串转义带来的问题。

内容来源于stack exchange