Match columns with multiple criteria using POI

Question

I'm trying to make a MATCH of columns using multiple criteria, as in the example below:

A1: Part   B1:  Code   C1:  Price   D1:  Find Part  E1:  Find Code
A2: x      B2:  11     C2:  5.00    D2:  y          E2:  12
A3: x      B3:  12     C3:  6.00
A4: y      B4:  11     C4:  7.00
A5: y      B5:  12     C5:  8.00

Where D2 and E2 are criterias. If D2 and E2 match with columns A and B respectively, I should get the result from corresponding column C. (In this example: 8.00)

For this example I tried the following Formulas on Excel:

=LOOKUP(D2&E2;A2:A5&B2:B5;C2:C5)
{=INDEX($C$2:$C$5;MATCH(D2&E2;$A$2:$A$5&$B$2:$B$5;0))}
=SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);INDEX(C2:C5;0;0))
=SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);C2:C5)

All of them give me the RIGHT result in Excel and OOCalc. But when using POI, I get errorcode 15 from the first one. Exception Unexpected eval type from the second, and Exception Invalid arg type for SUMPRODUCT from the third and last formulas.

Does anyone know how to match columns with multiple criteria by using a Formula which can be parsed by Apache POI?

Note: With Apache POI I'm successfully matching columns with one single criteria. So, I guess the error is not in my code, but maybe the format of those Formulas above aren't supported by POI.

Thanks in advance

Aprillion Aprillion · Accepted Answer · 2012-04-02T09:02:32

http://poi.apache.org/spreadsheet/formula.html says array formulas are not yet supported (your 1st, 3rd and 4th formulas are just aliases for array formulas, i'm affraid)

if you can insert a new column with formulas like =A2&B2 and match this new column, that would solve your problem..

Match columns with multiple criteria using POI

1 Answers