How to find max cell location

[Opal]

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

 

[Opal]

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range.  I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Just a little more information...perhaps there is an easier way
to get to what I want. I have a pivot table that shows downtime
for equipment in the shop. I have it set up to show only the top
3 incidents per week. I need to create a chart from the data
and my boss does not want a pivot chart. In the chart I need
to show not only the data, but the column heading. When
a breakdown occurs, our maintenance group classifies the
problem among several different reason codes which vary by
piece of equipment. These are the column headings. I
thought that if I could put in a formula that tells me the
location of the maximum value in the table I could then
use that info to point me to the correct column heading
to reference on the chart.....?? :-S Any advice would
be appreciated.

 

[Lars-Åke Aspelin]

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:AK9,0)-1,4)

Hope this helps / Lars-Åke

 

[Mike H]

Try

=ADDRESS(9,MATCH(MAX(B9:AK9),B9:AK9)+1)

Mike

 

[Opal]

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:AK9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

 

[Lars-Åke Aspelin]

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps / Lars-Åke

 

[Opal]

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps  / Lars-Åke- Hide quoted text -

- Show quoted text -

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people. So I have a part number in row 5
and reason codes in part 6. Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....

 

[Opal]

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people.  So I have a part number in row 5
and reason codes in part 6.  Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....- Hide quoted text -

- Show quoted text -

That's okay....got a work around recording a macro that copies, pastes
(transposes), deletes
blanks and copies to the cells where I need it.... Thanks for all
your help!