Cannot evaluate formulas with extra comma before missing optional parameter. In Microsoft Excel following formula is valid and evaluated: =MATCH("VAL",B3:B11,) In POI 4.1.2 evaluation throws RuntimeException. In POI 3.9 evaluation works. Steps to Reproduce: public static void main(String[] args) { XSSFWorkbook w = XSSFWorkbookFactory.createWorkbook(); XSSFSheet s = w.createSheet(); XSSFRow r = s.createRow(0); XSSFCell c = r.createCell(0); c.setCellFormula("MATCH(\"VAL\",B3:B11,)"); FormulaEvaluator evaluator = w.getCreationHelper().createFormulaEvaluator(); evaluator.evaluate(c); } Actual Results: Exception in thread "main" java.lang.RuntimeException: Unexpected match_type type (org.apache.poi.ss.formula.eval.MissingArgEval) at org.apache.poi.ss.formula.functions.Match.evaluateMatchTypeArg(Match.java:182) at org.apache.poi.ss.formula.functions.Match.evaluate(Match.java:77) at org.apache.poi.ss.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36) at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:153) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275) 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) Expected Results: Formula should be evaluated without Exceptions.
This should be fixed via r1884959, we now handle missing or blank match_type as default value "1".