Some sort of Lookup Function

R1chard

Active Member
Joined
Jan 14, 2004
Messages
404
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! :confused:
 

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
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jan 14, 2004
Messages
404

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
Joined
Jan 15, 2003
Messages
564
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
 

R1chard

Active Member
Joined
Jan 14, 2004
Messages
404

ADVERTISEMENT

thank you Tom
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jan 15, 2003
Messages
564
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. :cool:
 

Watch MrExcel Video

Forum statistics

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

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
Top