Find max and return corresponding value

[akhdiad]

Hi all, I am a new member to this forum. I have an excel problem and
hope all the experts in here can help me out.

A,B,C
Diameter,thickness,ID
762,25.4,1324
762,25.4,1325
660,19,1496
660,15.9,5000
762,35,1462
762,35,1464

Note: column separated by comma (,)

From the list, I would like to get two corresponding ID that would giv
the max diameter with the corresponding max thickness. For example firs
answer will be max diameter is (762) corresponding max thickness is (35
and ID (1462). second answer will be max diameter is (762) correspondin
max thickness is (35) and ID (1464).

Thanks.

 

[Ron Rosenfeld]

Hi all, I am a new member to this forum. I have an excel problem and I
hope all the experts in here can help me out.

A,B,C
Diameter,thickness,ID
762,25.4,1324
762,25.4,1325
660,19,1496
660,15.9,5000
762,35,1462
762,35,1464

Note: column separated by comma (,)

From the list, I would like to get two corresponding ID that would give
the max diameter with the corresponding max thickness. For example first
answer will be max diameter is (762) corresponding max thickness is (35)
and ID (1462). second answer will be max diameter is (762) corresponding
max thickness is (35) and ID (1464).

Thanks..

There are various methods to do this.

You could use an Advanced Filter; a VBA macro, or various formulas.

Here are some formulas. In the formulas below, Diameter refers to $A$2:$A$7; thickness and ID also refer to their respective columns.

**ASSUMPTIONS**
ID is numeric as it is in the data you present. If the ID is NOT numeric, the formula will need to be altered.
You are using a version of Excel that is 2007 or later. Again the formula will need to be altered if this is not the case.

This formula must be **array-entered**:

ID matching your criteria:
=IFERROR(LARGE(IF(thickness=MAX(IF(Diameter=MAX(
Diameter),thickness)),ID),ROWS($1:1)),"")
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If the above formula is in J2, then

Diameter corresponding to your criteria:
=IF(J2<>"",MAX(Diameter),"")

thickness corresponding to your criteria:
=IFERROR(INDEX(thickness,MATCH(J2,ID,0)),"")

Finally, select the three cells and fill down as far as required.