Lookup a value in a table and return the value in the first cell of the row

J

jday2011

New Member
Joined
May 27, 2012
Messages
9
So I am making a bowling stats spread sheet and am stuck with the last part of it. I need to look at all the scores of everyone in the league and return the persons name and score of the highest, 2nd highest, 3rd highest, and 4th highest for men and women

Right now I have the following times 10 teams

gender Team #1
m Jim 123 213 134
m Hank 166 111 215
m John 222 213 197
f Paula 100 145 98
f Jane 210 168 153

I have used the MAX formula to find the max value but cannot figure out how to display the name of the person who bowled that max value.

Any Help would be appreciated.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
jday2011 said:
So I am making a bowling stats spread sheet and am stuck with the last part of it. I need to look at all the scores of everyone in the league and return the persons name and score of the highest, 2nd highest, 3rd highest, and 4th highest for men and women

Right now I have the following times 10 teams

gender Team #1
m Jim 123 213 134
m Hank 166 111 215
m John 222 213 197
f Paula 100 145 98
f Jane 210 168 153

I have used the MAX formula to find the max value but cannot figure out how to display the name of the person who bowled that max value.

Any Help would be appreciated.
Click to expand...
Try something like this...

Book1
ABCDE
2mJim123213134
3mHank166111215
4mJohn222213197
5fPaula10014598
6fJane210168153
7_____
8_____
9_____
10M222John__
Sheet1

Enter this array formula** in B10:

=MAX(IF(A2:A6=A10,C2:E6))

Enter this array formula** in C10:

=INDEX(B:B,MAX(IF(A2:A6=A10,IF(C2:E6=B10,ROW(C2:E6)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy the first line and paste it at the start of all subsequent sheets in the same tab
Replies
4
Views
162
harzer
H
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
179
harzer
H
M
  • Solved
HELP---- Count Cells in a row based on Month and Unique cell contents.
2
Replies
16
Views
232
Peter_SSs
Peter_SSs
P
  • Solved
Formula Help IF Above Row Value is Greater than Below Row Value
Replies
6
Views
392
Peter_SSs
Peter_SSs
X
  • Question
Conditional formatting cells up to first cell after group of consecutive zeros
Replies
1
Views
297
awoohaw
awoohaw

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top