Hi,

Assuming target year in I1, target group in J1, value in K1, data in
A2:E10.

The following *array* formula (to be committed with Shift+Ctrl+Enter)
will calculate the quartile (assumption: in the first line example, a
value of $10, will be 2nd quartile).

=IF(ISNA(MATCH(K1,INDEX(C2:E10,MATCH(I1&J1,A2:A10& B2:B10,0),0),1)),1,1+MATCH(K1,INDEX(C2:E10,MATCH(I 1&J1,A2:A10&B2:B10,0),0),1))

HTH
Kostis Vezerides


TomorrowsMan wrote:
I have an Excel table that looks like this:

2006 G1 $10 $15 $20
2006 G2 $15 $21 $26
2006 G3 $21 $31 $41
2005 G1 $9 $14 $19
2005 G2 $14 $19 $25
2005 G3 $17 $22 $34
2004 G1 $8 $13 $18
2004 G2 $12 $17 $23
2004 G3 $15 $21 $31

Given a starting value, I need to return the quartile group based on
these criteria; below the minimum would be quartile 1, above the
maximum would be quartile 4. For instance:

Year Group Value Quartile
2006 G2 $22 3rd
2004 G1 $10 2nd
2005 G3 $37 4th
2006 G1 $9 1st


I have used Index/Match and Match/Offset before, but never a
combination of them, which I suspect is what I might need.

Thanks for assistance!

Chris