Retrieving the date in a MAX column

[mazorj]

I need help in writing a Function in Excel 2003.

The SS has dates running across the top of each column in Row 1, from Column D to Column IT, in DATE format to show dd/mm/year. In Row 6 of each column is a numeric value calculated from other cells in the column. I want to know both the highest value for that Row 6 AND the date on which it occurred.

Doing a MAX across Row 6 gives me the highest value, which I've placed in placed in cell IU6. What I want to do next is write a function in IU7 that looks back to the column with the MAX value in Row 6, looks to the top (Row 1) to find the date, then return that date to cell IU7. Any help is appreciated.

 

[T. Valko]

Try this:

=INDEX(D1:IT1,MATCH(IU6,D6:IT6,0))

Format as Date

Note that if there are duplicate max values the formula will return the date for the leftmost max value.

--
Biff
Microsoft Excel MVP


I need help in writing a Function in Excel 2003.

The SS has dates running across the top of each column in Row 1, from Column D to Column IT, in DATE format to show dd/mm/year. In Row 6 of each column is a numeric value calculated from other cells in the column. I want to know both the highest value for that Row 6 AND the date on which it occurred.

Doing a MAX across Row 6 gives me the highest value, which I've placed in placed in cell IU6. What I want to do next is write a function in IU7 that looks back to the column with the MAX value in Row 6, looks to the top (Row 1) to find the date, then return that date to cell IU7. Any help is appreciated.

 

[mazorj]

It works great! Thanks. I've adapted it to do similar ops on other rows, too.


Try this:

=INDEX(D1:IT1,MATCH(IU6,D6:IT6,0))

Format as Date

Note that if there are duplicate max values the formula will return the date for the leftmost max value.

--
Biff
Microsoft Excel MVP


I need help in writing a Function in Excel 2003.

The SS has dates running across the top of each column in Row 1, from Column D to Column IT, in DATE format to show dd/mm/year. In Row 6 of each column is a numeric value calculated from other cells in the column. I want to know both the highest value for that Row 6 AND the date on which it occurred.

Doing a MAX across Row 6 gives me the highest value, which I've placed in placed in cell IU6. What I want to do next is write a function in IU7 that looks back to the column with the MAX value in Row 6, looks to the top (Row 1) to find the date, then return that date to cell IU7. Any help is appreciated.

 

[dranon]

It works great! Thanks. I've adapted it to do similar ops on other rows, too.

Ah, teaching a man to fish!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


It works great! Thanks. I've adapted it to do similar ops on other rows, too.


Try this:

=INDEX(D1:IT1,MATCH(IU6,D6:IT6,0))

Format as Date

Note that if there are duplicate max values the formula will return the date for the leftmost max value.

--
Biff
Microsoft Excel MVP


I need help in writing a Function in Excel 2003.

The SS has dates running across the top of each column in Row 1, from Column D to Column IT, in DATE format to show dd/mm/year. In Row 6 of each column is a numeric value calculated from other cells in the column. I want to know both the highest value for that Row 6 AND the date on which it occurred.

Doing a MAX across Row 6 gives me the highest value, which I've placed in placed in cell IU6. What I want to do next is write a function in IU7 that looks back to the column with the MAX value in Row 6, looks to the top (Row 1) to find the date, then return that date to cell IU7. Any help is appreciated.

 

[mazorj]

Ah, teaching a man to fish!

Of course. Teaching others to fish is the goal of most newgroups...
as long as it's not trolling. ;-)