Your answer is one click away!

ManishChristian February 2016
### Get value from cell containing array formula

I have XSSFWorkbook and I've put **array formulas** in two column and I want to copy the values from these columns and paste onto other columns.

Formula looks like this:

```
{=IFERROR(INDEX(Sheet2!$A$2:$C$13,MATCH(1,(Sheet2!$A$2:$A$13=$A2)*(Sheet2!$C$2:$C$13=$D2),0),2),"FAIL")}
```

I have placed the formula (by running a loop on multiple cells) like this (sheet is XSSFSheet):

```
String formula = "AboveFormulaWithIterativeCell" // (without {} and =)
String range = "XFC" + i; // i is iterative row number
sheet.setArrayFormula(formula, CellRangeAddress.valueOf(range));
```

And then evaluated the formulas like this (wb is XSSFWorkbook):

```
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
```

Everything is working fine till this point. Formulas are displaying valid result.

Now if I run a loop on these cells and read the value like this (c is Cell):

```
String cellValue = c.getStringCellValue();
```

It always returning "FAIL", like it is returning the result after evaluating the formula as non-array formula.

So I've tried same thing with this formula:

```
{=IF(TRUE,TRUE,FALSE)}
```

And surprisingly it is returning me TRUE.

**So, my question is how this formula is returning correct result and not my main formula.**

So Apache Poi is still not supporting the manipulating table formulas (Array formulas).

Not yet supported Manipulating table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")

See **here** for more details.

So I had to convert my array formula to non-array formula.

**Note:**

You can still use array formulas, if you just want to display the result.

But you can not get the value from cell which contains array formula.

Asked in February 2016

Viewed 3,971 times

Voted 7

Answered 1 times

Viewed 3,971 times

Voted 7

Answered 1 times