Created attachment 36886 [details] Attached hereinwith is a sample excel file to reproduce the issue . Created a simple excel table in two sheets and called XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) on workbook . duplicate bug id - 57721 i have an excel file with named excel tables for vlookup in a sheet. im trying to get the vlookup result in another sheet by referencing through Gender_lookup table. I am using formula =VLOOKUP(TRIM(CLEAN([@[Gender ]])),Gender_lookup,2,0) which takes Gender value from current row in my table and checks for corresponding Gender value in Gender_lookup table in another sheet. I tried evaluating with XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook); which gives exception as org.apache.poi.ss.formula.FormulaParseException: The column doesn't exist in table Gender_lookup at org.apache.poi.ss.formula.FormulaParser.parseStructuredReference(FormulaParser.java:821) According to me the issue is related to named Table range inside Gender lookup table. I have checked the table name and its working perfectly in excel. Please help me with the issue without changing excel table formulaes. Im attaching a sample excel to reproduce the issue with screenshot.
Created attachment 36887 [details] Attached hereinwith is the screenshot for the Exception. Please find attached screenshot for the bug
Created attachment 36888 [details] Attached hereinwith is the java code to reproduce the issue Please find attached java file to reproduce issue. Please replace the excel path location in java file with your saved excel location.
Same issue with 5.2.3
Btw, there's a workaround for the issue: If you specify the range in the lookup table explicitly, both excel and the apache poi library can evaluate the formula just fine. E.g.: =VLOOKUP([@[Gender ]];Gender_lookup[[Gender input]:[Gender mod]];2;0)
According to Microsoft the structure-reference Gender_lookup[] is equivalent to Gender_lookup[#Data], which poi is able to parse correctly. See https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oe376/bcd72180-31a3-423b-8f83-d224b2286da3.
Since there's a workaround, imho this is not a very critical issue, however it would probably still be good to support this case. I opened a pull request to offer a fix for this issue: https://github.com/apache/poi/pull/514
https://github.com/apache/poi/pull/514 is now merged