Finding the column which a value was drawn from

[Guest]

I have a very large spreadsheet that I'm using the max and min functions
on. I need to know which column that the max and min are in. For example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

 

[Guest]

Conditional Formating =MAX($A1:$F1)

 

[Guest]

This will get you the column number for the max value in A3:F3:
=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

 

[Guest]

Thanks it worked great

This will get you the column number for the max value in A3:F3:
=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

 

[Ragdyer]

A bit less complicated:

Column number
=MATCH(MAX(A1:F1),A1:F1,0)

Column letter:
=ADDRESS(1,MATCH(MAX(A1:F1),A1:F1,0))

 

[Guest]

Great simplification, especially with the address formula. I knew there had
to be an easier way. The only thing to be careful about regarding your
column number solution is that the match function will return the relative
position in the range. Therefore if you don't start with column A, your
column number will have to be adjusted accordingly. The offset formula
(albeit more complicated) allows the starting point to change with the range.
Looking back at my formula, I am not sure why I had A1 instead of A3.

Best,
Dan