table lookup

J

Jhill

Can someone help me in creating a function to look up the following data:

Table:

Year 1 2 3 4 5 6
2,002 -137.15 -232.68 420.57 529.72 862.24 -103.23
2,003 985.12 985.05 999.49 982.1 1195.98 1005.19
2,004 274.52 547.62 468.69 442.84 337.74 561.39
2,005 474.75 502.9 516.04 445.04 103.68 870.25
2,006 574.68 506.82 506.59 502.93 57.6 213.25

I am trying to look up the min value per year and return the corresponding
number for that column. Example, I want the min value in 2002, to return (2)
because that is the period in which it fell. Then be able to do the same for
each year (min and max values, return the Period (#) which they fell.
 
D

Domenic

Assuming that A1:G6 contains the table, let I2:I6 contain 2002, 2003,
2004, etc., then try...

J2, copied down:

=INDEX($B$1:$G$1,MATCH(MIN(INDEX($B$2:$G$6,MATCH(I2,$A$2:$A$6,0),0)),INDE
X($B$2:$G$6,MATCH(I2,$A$2:$A$6,0),0),0))

For maximum, change MIN to MAX.

Hope this helps!
 
H

Herbert Seidenberg

If Values=$B$2:$G$6 and Periods=$B$1:$G$1
=MAX((MAX(Values 2:2)=Values 2:2)*Periods)
=MAX((MIN(Values 2:2)=Values 2:2)*Periods)
Enter with Ctrl+Shift+Enter, then copy down.
 
J

Jhill

Works great! Thanks!

Domenic said:
Assuming that A1:G6 contains the table, let I2:I6 contain 2002, 2003,
2004, etc., then try...

J2, copied down:

=INDEX($B$1:$G$1,MATCH(MIN(INDEX($B$2:$G$6,MATCH(I2,$A$2:$A$6,0),0)),INDE
X($B$2:$G$6,MATCH(I2,$A$2:$A$6,0),0),0))

For maximum, change MIN to MAX.

Hope this helps!
Click to expand...
 
Top