Formula Help

[markmol]

Hoping someone could help with this formula logic.


[TABLE="width: 420"]
<tbody>[TR]
[TD][/TD]
[TD]Tom[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Item #0[/TD]
[TD]50[/TD]
[TD]15[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Item #1[/TD]
[TD]65[/TD]
[TD]16[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Item #2[/TD]
[TD]80[/TD]
[TD]17[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]Item #3[/TD]
[TD]95[/TD]
[TD]18[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Item #4[/TD]
[TD]110[/TD]
[TD]19[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]Item #5[/TD]
[TD]125[/TD]
[TD]20[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Item #6[/TD]
[TD]140[/TD]
[TD]21[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Item #7[/TD]
[TD]155[/TD]
[TD]22[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]Item #8[/TD]
[TD]170[/TD]
[TD]23[/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]Item #9[/TD]
[TD]185[/TD]
[TD]24[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]Item #10[/TD]
[TD]200[/TD]
[TD]25[/TD]
[TD]190[/TD]
[/TR]
</tbody>[/TABLE]


Enter Criteria (input): Joe

Output: Largest number within Joe numbers. aka (190)

What I want to accomplish is to be able to type in any names and the formula looks up the largest value only within that persons column.
I've been trying to combine IF and LARGE formulas and I think I'm pretty close, I just can't finish it.

Any input is appreciated. Thanks.

 

[63falcondude]

In cell F2 type Joe. In cell G2 type

=MAX(IF(B1:D1=F2,B2:D12))

Enter it with Ctrl Shift Enter

 

[markmol]

What if i wanted to return the item name in column A instead of the largest value? Any idea?

 

[63falcondude]

Yea, give me a minute.

 

[63falcondude]

If you want to know the item number corresponding to the max value for Joe, you can use

G2 =INDEX(A:A,MATCH(MAX(IF(B1:D1=F2,B2:D12)),D2:D12,0))

Ctrl Shift Enter

 

[markmol]

Nope doesn't seem to work.

 

[63falcondude]

Better formula:

F2 manually entered
G2 {=MAX(IF(B1:D1=F2,B2:D12))}
H2 {=IF(F2=B1,INDEX(A:A,MATCH(G2,B2:B12,0)),IF(F2=C1,INDEX(A:A,MATCH(G2,C2:C12,0)),IF(F2=D1,INDEX(A:A,MATCH(G2,D2:D12,0)))))}

This changes depending on the name that you type into F2

 

[markmol]

That formula is only good for Joe, correct? So i was to type in any other name in the criteria cell it would still only return values from column D

 

[63falcondude]

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item #[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Max Value[/TD]
[TD="align: center"]Item[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Item #0[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]Item #9[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Item #1[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Item #2[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Item #3[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Item #4[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]130[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Item #5[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Item #6[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Item #7[/TD]
[TD="align: center"]155[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Item #8[/TD]
[TD="align: center"]170[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]170[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Item #9[/TD]
[TD="align: center"]185[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Item #10[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]190[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Enter the formulas in G2 and H2

G2 =MAX(IF(B1:D1=F2,B2:D12)) Ctrl Shift Enter
H2 =IF(F2=B1,INDEX(A:A,MATCH(G2,B2:B12,0)),IF(F2=C1,INDEX(A:A,MATCH(G2,C2:C12,0)),IF(F2=D1,INDEX(A:A,MATCH(G2,D2:D12,0))))) Ctrl Shift Enter

 

[63falcondude]

That formula is only good for Joe, correct? So i was to type in any other name in the criteria cell it would still only return values from column D

That was the case with my first attempt. Post #9 should work for you.