1

I have an Excel file with named excel tables for VLOOKUP in a sheet.

I'm trying to get the VLOOKUP result in another sheet by referencing through the Gender_lookup table. I am using the 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. The formula is valid and working in excel.

I have also raised an issue in bugzilla for reference with all attachments

I tried evaluating with XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) which gives me the exception:

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)
    at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:908)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:498)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:322)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1548)
    at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1506)
    at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1493)
    at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1867)
    at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1994)
    at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1978)
    at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1935)
    at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1908)
    at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1479)
    at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:1352)
    at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:905)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:498)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:322)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1548)
    at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1506)
    at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1493)
    at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1867)
    at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1994)
    at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1978)
    at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1935)
    at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1908)
    at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1889)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:2036)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
    at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:85)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:271)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
    at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:110)
    at practcie.Vlookup.main(Vlookup.java:97)
Sean Bright
  • 118,630
  • 17
  • 138
  • 146
Niketan Mishra
  • 21
  • 3
  • 1
    The apache poi formula parser is not able parsing the structured reference `TableName` to be a reference for `TableName[#Data]`. If the formula would be `=VLOOKUP(TRIM(CLEAN([@[Gender]])),Gender_lookup[#Data],2,0)` then formula parser will succeed. – Axel Richter Nov 19 '19 at 16:17
  • Hi Axel Richter, when i read the formula from apche poi it reads it as VLOOKUP(TRIM(CLEAN(Member_Data[[#This Row],[Gender mod]])),Gender_lookup[],2,0) ...Here Member_data is the name of the row table where i have written this formula and Gender_lookup is name of other table in 2nd sheet. i find nothing wrong in here..could u please elaborate on this once again – Niketan Mishra Nov 19 '19 at 16:40
  • It is a bug in `apache poi`. The `Excel` formula `=VLOOKUP(TRIM(CLEAN([@Gender])),Gender_lookup,2,0)` is `=VLOOKUP(TRIM(CLEAN(Member_Data[[#This Row],[Gender]])),Gender_lookup[#Data],2,0)` when all structured references are fully given and the table having column `Gender` is named `Member_Data`. But `apache poi`'s formula parser gets `Gender_lookup` as `Gender_lookup[]`instead of `Gender_lookup[#Data]`. – Axel Richter Nov 19 '19 at 16:48
  • Thank you so Much Axel it worked..But is there any alternate solution without alterning formula in excel through java – Niketan Mishra Nov 19 '19 at 17:14

0 Answers0