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]

Just noticed that H2 should say Item #10...

Change A:A to A2:A12 so your formula in H2 would be
=IF(F2=B1,INDEX(A2:A12,MATCH(G2,B2:B12,0)),IF(F2=C1,INDEX(A2:A12,MATCH(G2,C2:C12,0)),IF(F2=D1,INDEX(A2:A12,MATCH(G2,D2:D12,0))))) Ctrl Shift Enter

 

[markmol]

Thanks! That works but that is just an example and i have over 90 Criteria so that formula would be too long.

 

[jeffreybrown]

How about...

G1 =LARGE(INDEX(B2:D12,,MATCH(F1,B1:D1,0)),1)
H1 =INDEX(A2:A12,MATCH(G1,INDEX(B2:D12,,MATCH(F1,B1:D1,0))),0)

       ---A---- -B- -C- -D- E -F- -G- ---H----
   1            Tom Bob Joe   Tom 200 Item #10
   2   Item #0  50  15  90                    
   3   Item #1  65  16  100                   
   4   Item #2  80  17  110                   
   5   Item #3  95  18  120                   
   6   Item #4  110 19  130                   
   7   Item #5  125 20  140                   
   8   Item #6  140 21  150                   
   9   Item #7  155 22  160                   
  10   Item #8  170 23  170                   
  11   Item #9  185 24  180                   
  12   Item #10 200 25  190

Note: No need for CSE on either of these formulas