table lookup

[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.

 

[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!

 

[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.

 

[Jhill]

Works great! Thanks!

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!