Match Offset by more than one value

[TomorrowsMan]

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

 

[vezerid]

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(I1&J1,A2:A10&B2:B10,0),0),1))

HTH
Kostis Vezerides

 

[TomorrowsMan]

Perfect - thank you!!

 

[vezerid]

Thanks for the feedback.

Kostis

 

[TomorrowsMan]

Kostis,

Perhaps I can bother you one more time? I'm stumped on the last part
of what I'm trying to do.

Now that you have helped me successfully acquire the quartile, I am
trying to do one last lookup: I have my Year, Quartile, Percentage,
and Rating in a table as below. This is very simplified:

Year Quartile Percentage Rating
2006 4 2.5% 3.5
2006 2 3.0% 4
2005 1 2.0% 2
2005 1 2.0% 2.5
2004 2 2.0% 1
2004 1 2.5% 2
2003 3 2.5% 2
2003 2 3.0% 3

How would I go about returning the MAX Rating value based on the other
three criteria? I say 'MAX' because some combinations of Year,
Quartile, and Percentage may have more than one Rating value (the 2005
rows in the example above).

I tried this array formula, assuming the table above is in columns A-D,
and my lookup data is in J1 (Year) J2 (Quartile) and J3 (Percentage):


{=INDEX($D$1:$D$8,MATCH(0,($A$1:$A$8=J1)*($B$1:$B$8=J2)*($C$1:$C$8=J3)),1)}

I feel like I'm missing something, an offset perhaps, but I'm not sure
what....

Thanks again!