Some sort of Lookup Function

R1chard

Active Member
Joined
Jan 14, 2004
Messages
407
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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."))
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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