Return Column Range Name 2

[wrbnoh]

How can I return the column name range from a row where I have found the highest vaule?


Example

I have rows of products and columns of years. The data is sales. I have returned the highest sales value and want to return the corresponding year in which those sales occurred.

 

[SkipVought]

Hi,

How did you get the VALUE?

Where do you want the COLUMN HEADING returned to?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wrbnoh]

"How did you get the VALUE?"

I used the @Large function.

"Where do you want the COLUMN HEADING returned to?"
Example:
Columns C through J contain Sales Figures
Column K contains the @large value of C:J
I want Column L to contain the Column Name Range from the result of Column K (so if Column E contained the highest value I want to return E's range name in Column L)

I hope I have explained this properly.

 

[SkipVought]

=CHAR(MATCH(K1,C1:J1,0)+COLUMN(C1)+63)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[arthurbr]

You can also try :

=INDIRECT(ADDRESS(1;MATCH(K3;C3:J3;0)+COLUMN(C2)-1;3;1))

 

[wrbnoh]

a b c d e f g h i
1 2000 1999 1998 1997 1996 1995 High Sales HS Yr
2 apple 50 20 10 30 60 40 60 ?
3 pear 10 80 90 30 20 50 90
4 peach 60 40 30 20 80 10 80

cell h2 = Large(b2:g2,1)

How can I return in i2 the year in which the high sales occured?
(in the above example I would want to return "1996&quot

 

[SkipVought]

=INDEX($B$1:$G$1,1,MATCH(H2,B2:G2,0))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wrbnoh]

=INDEX($B$1:$G$1,1,MATCH((LARGE($B2:$G2,1)),$B2:$F2,0))

Is the winner.

Thanks all.

 

[wrbnoh]

Thanks Skip

 

[arthurbr]

Thank you ,wrbnoh, for letting us do some searching and after that changing your example and not even looking at the answers

 

[VBAjedi]

arthurbr,

I see you are new to Tek-Tips. Welcome! Just a friendly heads-up:

Beginners and "dumb" questions are welcomed here, but sarcasm is generally not (unless it's between members that interact often). Violations of forum etiquette are either tolerated or politely corrected. For example: even though it only takes three mouse-clicks to award a star, only about 10% of even the top experts posts are ever acknowledged with a star (and I can assure you, a far higher percentage of their posts are helpful!). But those experts continue to provide excellent assistance, day in and day out.

If you would like to share your expertise with others in these forums, know that you will sometimes be misunderstood, underestimated, underappreciated, or even ignored. It's a side-effect of working with beginners. Those of us who choose to do it anyway have discovered that the feeling when you help someone "get it" makes all the rest more than worth it.

I hope you do decide to stick around!

VBAjedi