Excel - Find Min and lookup? 2

[psarros]

Hello

I have a list of Tickers and thier price on given dates, and I need to find the min and max and when the min and max occured.

Is there a way to do a vlookup or Hlookup and find a number and return the column it is in?

my data is in the following form

Ticker date1 date2 date3
IBM 10.00 20.00 30.00
DELL 7.00 12.00 9.00


Thanks

 

[ETID]

assuming that "Ticker" header is in cell A1
then in cell e2...

=INDIRECT(ADDRESS(1,MATCH(MIN(B22),B22,0)+1))


fill down

 

[ETID]

Use this for the Max date


=INDIRECT(ADDRESS(1,MATCH(MAX(B22),B22,0)+1))

 

[KenWright]

psarros, what happens when your data look like this?

Ticker date1 date2 date3 date4 date5 date6 date7
IBM 10.00 20.00 30.00 25.00 25.00 20.00 30.00
DELL 7.00 12.00 9.00 9.00 8.00 9.00 12.00

What dates would you expect to be returned?

Regards
Ken...............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[byundt]

I suggest using ETID's formulas, only with the INDEX function rather than ADDRESS & INDIRECT. INDIRECT is a volatile function that recalculates every time something changes on the worksheet. INDEX is not, and so will only recalc when it needs to. As a result, a big spreadsheet will seem a little more responsive.
=INDEX($1:$1,1,MATCH(MIN(2:2),2:2,0)) Formula for MIN date
=INDEX($1:$1,1,MATCH(MAX(2:2),2:2,0)) Formula for MAX date
These formulas assume that the dates are in row 1 and that the formulas are not placed in the same row as the data.

If the formulas are placed in the same row as the data, you'll have to specify the columns like ETID did:
=INDEX($B$1:$D$1,1,MATCH(MIN($B2:$D2),$B2:$D2,0)) Formula for MIN date
=INDEX($B$1:$D$1,1,MATCH(MAX($B2:$D2),$B2:$D2,0)) Formula for MAX date