Bug 63934

Summary: The column doesn't exist in lookup table
Product: POI Reporter: niketan mishra <niketan.mishra1995>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: critical CC: niketan.mishra1995
Priority: P2    
Version: 4.1.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 57721    
Bug Blocks:    
Attachments: 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 .
Attached hereinwith is the screenshot for the Exception.
Attached hereinwith is the java code to reproduce the issue

Description niketan mishra 2019-11-19 09:39:21 UTC
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.
Comment 1 niketan mishra 2019-11-19 09:43:01 UTC
Created attachment 36887 [details]
Attached hereinwith is the screenshot for the Exception.

Please find attached screenshot for the bug
Comment 2 niketan mishra 2019-11-19 09:45:59 UTC
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.