distribute points according to results of the stage

S

strooman

Active Member
Joined
Oct 29, 2013
Messages
333
Office Version
  1. 2016
Platform
  1. Windows
I want to distribute points among the riders according to the ranking of the stage. The lay-out:

Ranking of the stage in column A:E
My rider selection in column G:J
Points to display in column L:Q (Here I want to display the results, the points)

When a team in column J is present in column D give the rider it's points in column L (according to the fixed point chart in E).
Example, ARLEY BERNAL GOMEZ Egan is in TEAM INEOS so he get's 17 points because TEAM INEOS is second place in the ranking.

Additional, when a rider is in the winning team (which is TEAM JUMBO - VISMA) that rider get's an additional 5 points. Display that in Column N

Question, what formula do I need in column L and column N to accomplish that?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][td]
O​
[/td][td]
P​
[/td][td]
Q​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#70AD47"]RiderId[/td][td="bgcolor:#70AD47"]Rank[/td][td="bgcolor:#70AD47"]Rider[/td][td="bgcolor:#70AD47"]Team[/td][td="bgcolor:#70AD47"]Points[/td][td][/td][td="bgcolor:#70AD47"]RiderId[/td][td="bgcolor:#70AD47"]Rider[/td][td="bgcolor:#70AD47"]Status[/td][td="bgcolor:#70AD47"]Team[/td][td][/td][td="bgcolor:#0070C0"]Stage[/td][td="bgcolor:#0070C0"]Team[/td][td="bgcolor:#0070C0"]Mountain[/td][td="bgcolor:#0070C0"]Sprint[/td][td="bgcolor:#0070C0"]Top5[/td][td="bgcolor:#0070C0"]Penalty[/td][/tr]
[tr][td]
2​
[/td][td="bgcolor:#E2EFDA"]
81​
[/td][td="bgcolor:#E2EFDA"]
1​
[/td][td="bgcolor:#E2EFDA"]KRUIJSWIJK Steven[/td][td="bgcolor:#E2EFDA"]TEAM JUMBO - VISMA[/td][td="bgcolor:#E2EFDA"]
20​
[/td][td][/td][td="bgcolor:#E2EFDA"]
2​
[/td][td="bgcolor:#E2EFDA"]ARLEY BERNAL GOMEZ Egan[/td][td="bgcolor:#E2EFDA"]Leader[/td][td="bgcolor:#E2EFDA"]TEAM INEOS[/td][td][/td][td]
17​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td]
1​
[/td][td]
2​
[/td][td]THOMAS Geraint[/td][td]TEAM INEOS[/td][td]
17​
[/td][td][/td][td]
86​
[/td][td]MARTIN Tony[/td][td]member[/td][td]TEAM JUMBO - VISMA[/td][td][/td][td]
20​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
4​
[/td][td="bgcolor:#E2EFDA"]
21​
[/td][td="bgcolor:#E2EFDA"]
3​
[/td][td="bgcolor:#E2EFDA"]ALAPHILIPPE Julian[/td][td="bgcolor:#E2EFDA"]DECEUNINCK - QUICK - STEP[/td][td="bgcolor:#E2EFDA"]
14​
[/td][td][/td][td="bgcolor:#E2EFDA"]
31​
[/td][td="bgcolor:#E2EFDA"]BARDET Romain[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]AG2R LA MONDIALE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
5​
[/td][td]
141​
[/td][td]
4​
[/td][td]MATTHEWS Michael[/td][td]TEAM SUNWEB[/td][td]
11​
[/td][td][/td][td]
84​
[/td][td]GROENEWEGEN Dylan[/td][td]member[/td][td]TEAM JUMBO - VISMA[/td][td][/td][td]
20​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td="bgcolor:#E2EFDA"]
181​
[/td][td="bgcolor:#E2EFDA"]
5​
[/td][td="bgcolor:#E2EFDA"]ZAKARIN Ilnur[/td][td="bgcolor:#E2EFDA"]TEAM KATUSHA ALPECIN[/td][td="bgcolor:#E2EFDA"]
8​
[/td][td][/td][td="bgcolor:#E2EFDA"]
41​
[/td][td="bgcolor:#E2EFDA"]NIBALI Vincenzo[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]BAHRAIN - MERIDA[/td][td][/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]
91​
[/td][td]
6​
[/td][td]URAN Rigoberto[/td][td]EF EDUCATION FIRST[/td][td]
5​
[/td][td][/td][td]
81​
[/td][td]KRUIJSWIJK Steven[/td][td]member[/td][td]TEAM JUMBO - VISMA[/td][td][/td][td]
20​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td="bgcolor:#E2EFDA"]
111​
[/td][td="bgcolor:#E2EFDA"]
7​
[/td][td="bgcolor:#E2EFDA"]VAN AVERMAET Greg[/td][td="bgcolor:#E2EFDA"]CCC TEAM[/td][td="bgcolor:#E2EFDA"]
4​
[/td][td][/td][td="bgcolor:#E2EFDA"]
101​
[/td][td="bgcolor:#E2EFDA"]YATES Adam[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]MITCHELTON - SCOTT[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td]
51​
[/td][td]
8​
[/td][td]PINOT Thibaut[/td][td]GROUPAMA - FDJ[/td][td]
3​
[/td][td][/td][td]
11​
[/td][td]SAGAN Peter[/td][td]member[/td][td]BORA - HANSGROHE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#E2EFDA"]
41​
[/td][td="bgcolor:#E2EFDA"]
9​
[/td][td="bgcolor:#E2EFDA"]NIBALI Vincenzo[/td][td="bgcolor:#E2EFDA"]BAHRAIN - MERIDA[/td][td="bgcolor:#E2EFDA"]
2​
[/td][td][/td][td="bgcolor:#E2EFDA"]
141​
[/td][td="bgcolor:#E2EFDA"]MATTHEWS Michael[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]TEAM SUNWEB[/td][td][/td][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td]
71​
[/td][td]
10​
[/td][td]FUGLSANG Jakob[/td][td]ASTANA PRO TEAM[/td][td]
1​
[/td][td][/td][td]
21​
[/td][td]ALAPHILIPPE Julian[/td][td]member[/td][td]DECEUNINCK - QUICK - STEP[/td][td][/td][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
71​
[/td][td="bgcolor:#E2EFDA"]FUGLSANG Jakob[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]ASTANA PRO TEAM[/td][td][/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
111​
[/td][td]VAN AVERMAET Greg[/td][td]member[/td][td]CCC TEAM[/td][td][/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
121​
[/td][td="bgcolor:#E2EFDA"]MARTIN Daniel[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]UAE TEAM EMIRATES[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
1​
[/td][td]THOMAS Geraint[/td][td]member[/td][td]TEAM INEOS[/td][td][/td][td]
17​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
122​
[/td][td="bgcolor:#E2EFDA"]ARU Fabio[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]UAE TEAM EMIRATES[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
17​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
62​
[/td][td]VALVERDE Alejandro[/td][td]member[/td][td]MOVISTAR TEAM[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
18​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
161​
[/td][td="bgcolor:#E2EFDA"]EWAN Caleb[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]LOTTO SOUDAL[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
61​
[/td][td]QUINTANA Nairo[/td][td]member[/td][td]MOVISTAR TEAM[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
20​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
88​
[/td][td="bgcolor:#E2EFDA"]VAN AERT Wout[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]TEAM JUMBO - VISMA[/td][td][/td][td]
20​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
21​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
51​
[/td][td]PINOT Thibaut[/td][td]member[/td][td]GROUPAMA - FDJ[/td][td][/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
22​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
28​
[/td][td="bgcolor:#E2EFDA"]VIVIANI Elia[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]DECEUNINCK - QUICK - STEP[/td][td][/td][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
23​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
126​
[/td][td]KRISTOFF Alexander[/td][td]member[/td][td]UAE TEAM EMIRATES[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
24​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E2EFDA"]
181​
[/td][td="bgcolor:#E2EFDA"]ZAKARIN Ilnur[/td][td="bgcolor:#E2EFDA"]member[/td][td="bgcolor:#E2EFDA"]TEAM KATUSHA ALPECIN[/td][td][/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
25​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
211​
[/td][td]BARGUIL Warren[/td][td]member[/td][td]TEAM ARKEA - SAMSIC[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
How about in L2
=IFERROR(INDEX($E$2:$E$11,MATCH(J2,$D$2:$D$11,0)),"")
and M2
=IF(J2=$D$2,5,"")
 
Upvote 0
Ha ha, thank you very much Fluff. Sometimes it's so simpel but for the moment I couldn't see the wood for the trees and I focussed to much on the details.
Your solutions works perfect. Thanks.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How many times occurs a team from column A in column D and then multiply and sum
Replies
2
Views
575
strooman
S

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

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