Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Match Offset by more than one value
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 |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Match Offset by more than one value
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 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Match Offset by more than one value
Perfect - thank you!!
|
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Match Offset by more than one value
Thanks for the feedback.
Kostis TomorrowsMan wrote: Perfect - thank you!! |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Match Offset by more than one value
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If And Match With a MAX and Offset | Excel Discussion (Misc queries) | |||
MATCH and OFFSET | Excel Worksheet Functions | |||
Index - Offset - Match Issues | Excel Worksheet Functions | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |