poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rafael Ribeiro Rezende <rafaelrez...@gmail.com>
Subject Match columns with multiple criteria using POI
Date Sun, 25 Mar 2012 18:42:21 GMT
Hi all,

Day ago I made a question about a problem I had while evaluating formulas.
But now I would like to focus on the solution for matching columns with
multiple criteria, instead of trying to solve that specific formula I
posted. I don't know if I should reply on that, but since it's now a more
general question, I decided to create a new thread. I hope it's ok.

Well, 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

-- 
Rafael R Rezende

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message