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

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

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

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

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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,697
Messages
5,855,226
Members
431,712
Latest member
Stella J

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.

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

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