find maximum of two values in an array with same lookup value

G

Guest

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome
 
G

Guest

Are you looking for the highest total score for all competitions, the highest
score in any competition or the highest score for an individual competition
seen by the team?

do you want an indication there is a tie if one is present?
 
G

Guest

Data arranged something like

Game 1 Game 2 Game 3
Team 1 Player 1 123 134 101
Team 1 Player 2 132 150 90
Team 2 Player 3 102 150 105
Team 2 Player 4 95 149 107

For each game I want to be able to locate the highest score recorded by
whichever team member, so in another table i'm looking for

Game 1 Game 2 Game 3
Team 1 132 150 101
Team 2 102 150 107

where the game scores in the "team only" table are the higher of the two
results recorded by that teams players. You can assume a tie between players
in the same team is not possible.
 
G

Guest

I think I've got this - if I create a hidden column (to the left) of the
team and player names which contains a concatenation of Team and Player
I can then create tables elsewhere in the work book something like this.

A B C D
1 Team 1 Player 1 Player 2 =max(vlookup(A1&B1,<lookup
range
name>,<column
offset>,true),vlookup(A1&C1,<lookup range name>,<column offset>,true))

which I think does the job!!

Thanks to those who gave it some thought!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need V-Lookup to Return A Row, Not Just Single Value 2
SUMIF problem... 2
How to determine second (and then third) highest value in a list 2
list items from one table in another table based on value in first table 2
Lookup Multiple Criteria 4
Use array for lookup value, to return array of lookups 4
Counting the number of matching items in a query 2
Multiple instances of lookup value 3

Top