CODEWITHSUNDEEP
excel
AreYouSure
AreYouSure

Reputation: 732

Sum of values in a lookup

I have multiple columns (A & B) that do a VLOOKUP to find the position of a player in a race.

=IFERROR(VLOOKUP(B2,'Semi Final 1'!$B$3:$C$11,2,FALSE),"")
=IFERROR(VLOOKUP(B2,'Semi Final 1'!$B$11:$C$23,2,FALSE),"")

Depending on a players position, they get a set number of points that is listed on another sheet.

1 | 25
2 | 23
etc (Where | is a new column)

I then have a column where I want to take each position, reference this other sheet to see how many points they should get, then add those two values together.

So for example, if a player finished 1st in both races, they would have 50 points.

I've looked ta INDEX, MATCH, and other lookup functions but I'm struggling to get my head around it.

Upvotes: 0

Views: 69

Answers (1)

ABowering
ABowering

Reputation: 47

Seems like a simple problem, but tell us how you want to layout your workbook so that the answers can be more specific.

I am assuming you have a summary page of all the players and races and their positions. Then from there you would lookup each of the finishing positions and assign a number of points then find a total.

So your results page would look like:

    Race            
Player  Semi 1  Semi 2  Final
Tom     1       3       3   
Harry   2       2       1   
Fred    3       1       2   
…

You would also have a lookup table something like this:

Position Points
1        25
2        23
…

From there you could do a lookup on each player and race to find the total number of points:

    Race            
Player  Semi 1  Semi 2  Final   Total
Tom     25      21      21      67
Harry   23      23      25      71
Fred    21      25      23      69
…

In each of the points you would have a formula similar to the one you posted:

=IFERROR(VLOOKUP(Position,Lookup range,2,FALSE),"")

Then sum all the points in a total column.

Upvotes: 1

Related Questions