CODEWITHSUNDEEP
excelmax
user3680304
user3680304

Reputation: 23

How to Find a Max and then see which column it is attributed to

I am taking a max value between CD2 and CR2 using =MAX(CD2:CR2) and instead of going through manually to find which column holds the max, is there a way for it to return this value?

Thanks!

Upvotes: 1

Views: 76

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Use the MATCH function.

For example, the following formula returns address of first entry of max value in range CD2:CR2 (returns $CF$2 for max in CF2):

=CELL("address",INDEX(CD2:CR2,1,MATCH(MAX(CD2:CR2),CD2:CR2,0)))

enter image description here

Alternatively you can return relative column number (starting from CD):

=MATCH(MAX(CD2:CR2),CD2:CR2,0)

i.e. formula returns 3 for above image - since CF - 3rd column from CD

For absolute column number:

=COLUMN($CD$2)+MATCH(MAX(CD2:CR2),CD2:CR2,0)-1

and non-volatile version:

=COLUMNS($A$2:$CD$2)+MATCH(MAX(CD2:CR2),CD2:CR2,0)-1

both formulas return 84 for max in CF2.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Consider:

=MID(CELL("address",INDEX(CD2:CR2,1,MATCH(MAX(CD2:CR2),CD2:CR2,0))),2,2)

Upvotes: 0

Related Questions