# Some sort of Lookup Function

#### R1chard

##### Active Member
I have a list os scores in cells E13:E17 with a name next to tem from cells F13:G17

then cells H13:H17 i have the word Vs

Cells I13:I17 have names in and cells J13:J17 have points in.

The table looks abit like this

Points............Name.......................................Name..................Points
20...............PlayerA................vs..................PlayerB..................17
22...............PlayerC................vs..................PlayerD..................35
18...............PlayerE................vs..................PlayerF...................40

and so on

No i need something to search all the cells with points in and the person with the highest number points i want their name to display in Cell G29

in this case G29 would display PlayerF because they have the highest number of points!

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Joe4

##### MrExcel MVP, Junior Admin
My guess is that you will have to use some combination of the MAX function along with the INDEX and MATCH functions. I am not proficient enough in these formulas to figure it out.

However, due to the basic structure of your spreadsheet, this is going to be more complicated than it has to be. You should design your table so that the players name is ALWAYS to the right of the score, or always to the left of the score. By designing it to have score on the left for some people, and on the right for other people is going to add a whole other layer of complexity to what will already be a complex formula.

#### just_jon

##### Legend
You didn't say what you wanted to do with ties. The below alerts you to ties between columns but not to a tie within a column, which could be rixed.
Book1
EFGHIJ
1PointsNameNamePoints
220PlayerAvsPlayerB17
322PlayerCvsPlayerD35
418PlayerEvsPlayerF40
5
6
7
8PlayerF
Sheet1

in E8: =IF(MAX(E2:E4)>MAX(J2:J4),INDEX(F2:F4,MATCH(MAX(E2:E4),E2:E4,0),0),IF(MAX(J2:J4)>MAX(E2:E4),INDEX(I2:I4,MATCH(MAX(J2:J4),J2:J4,0),0),"There is a tie in the 2 columns."))

#### R1chard

##### Active Member
Thankyou it worked

#### R1chard

##### Active Member

ADVERTISEMENT

From the excel image you posted, Jon, the person with the highest points is displayed in cell ??

If i wanted the person with the second highest points to display in the cell below, what formula would i use?

Then the 3rd highest place in the next cell down and so on. What would i need to change from that IF formula to make this happen?

#### rrdonutz

##### Well-known Member
Hi Jon--

You've probably already caught it if you're responding to R1chard's latest request, but your formula needs to be modified (I think) in case there is a tie in the same column. For example, if player D also has 40 points, then no tie is indicated. One possible work-around:

=IF(COUNTIF(E2:J4,MAX(E2:J4))>1,"There is a tie",IF(MAX(E2:E4)>MAX(J2:J4),INDEX(F2:F4,MATCH(MAX(E2:E4),E2:E4,0)),INDEX(I2:I4,MATCH(MAX(J2:J4),J2:J4,0))))

--Tom

ADVERTISEMENT

thank you Tom

#### just_jon

##### Legend
Kind of busy at the moment; have a peek at the LARGE or RANK function. Will try to stop back in my afternoon, if not someone will pick you up.

#### just_jon

##### Legend
Gawd-awful hideous! Avert your eyes! Do not look directly into the formula...
Book2.xls
EFGHIJ
1PointsNameNamePoints
220PlayerAvsPlayerB17
322PlayerCvsPlayerD35
418PlayerEvsPlayerF40
5
6
7
81PlayerF
92PlayerD
103PlayerC
Sheet1

in F9: =IF(COUNTIF(\$E\$2:\$J\$4,LARGE(\$E\$2:\$J\$4,\$E9))>1,"There is a tie",IF(ISNUMBER(MATCH(LARGE(\$E\$2:\$J\$4,\$E9),\$E\$2:\$E\$4,0)),INDEX(\$F\$2:\$F\$4,MATCH(LARGE(\$E\$2:\$J\$4,\$E9),\$E\$2:\$E\$4,0),0),INDEX(\$I\$2:\$I\$4,MATCH(LARGE(\$E\$2:\$J\$4,\$E9),\$J\$2:\$J\$4,0),0)))

Hey Tom! Come fix what I did to your formula; give the poor guy an array...

#### rrdonutz

##### Well-known Member
Hey Tom! Come fix what I did to your formula; give the poor guy an array...
Jon, no can do! Looks like you've come up with more than I'm willing (or able) to do at this point, and I'm outta here tonight (5:40 PM here in cenTex) . . . Maybe one of the other (than yourself) resident geniuses here can improve it.

Replies
9
Views
103
Replies
52
Views
814
Replies
3
Views
94
Replies
4
Views
111
Replies
21
Views
288

Threads
1,130,329
Messages
5,641,546
Members
417,219
Latest member
FrancoisT

### 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

### 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