# Thread: Max Values In Columns (2002)

1. ## Max Values In Columns (2002)

Dear Helpers,
Need a formula to do this:
Look in Col A, get max value. Say this is 8. There can be many 8's.
Look in Col B, get max value when Col A = 8.
The attached file shows this need.
Thank you.

2. ## Re: Max Values In Columns (2002)

If there are more than 1 value in col A that is the max, which value in col B do you want? Do you want the max from col B that corresponds to the 8 or do you want the FIRST one found or something else? The 50 you list is the MAX of 50 and 20 and it is ALSO the first one found so I am not sure which one to pick.

Steve

3. ## Re: Max Values In Columns (2002)

This will give the FIRST value in B that corresponds with the max in A:
="The max value in Col A is "&MAX(A1:A10)&" and corresponding Col B max value is "&INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))

This will give you the MAX value in B which corresponds to the MAX in A. (It is an ARRAY formula, confirm with ctrl-shift-enter):
="The max value in Col A is "&MAX(A1:A10)&" and corresponding Col B max value is "&MAX(IF(A1:A10=MAX(A1:A10),B1:B10))

Change ranges as appropriate
Steve

4. ## Re: Max Values In Columns (2002)

The maximum value in A1:A10 is =MAX(A1:A10)

The maximum value in B1:B10 for cells with the maximum value in A1:A10 is =MAX(IF(A1:A10=MAX(A1:A10),B1:B10,"")) entered as an array formula (confirm with Ctrl+Shift+Enter).

5. ## Re: Max Values In Columns (2002)

Thanks Steve.
It is the second solution that I am looking for. Great help.
Selva.

6. ## Re: Max Values In Columns (2002)

Thanks Hans. Problem solved. Great assistance.
Selva.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•