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
