Selecting a corresponding value from a range

[Vanvliet247]

I am trying to auto-select points which correspond to finishing positions in a race. First position 25 points, second position 20 points, third position 16 points etc. About 15 different point levels in total. Column 1 will be the finishing positions, column 2 the points corresponding to those. Can anyone advise a formula for column 2 - everything I've tried so far hasn't worked.

Thanks.

[#2 · Accepted solution]

@Vanvliet247 

=SWITCH(A2,1,24,2,20,3,16,4,12,5,11,6,10,7,9,8,8,9,7,10,6,11,5,12,4,13,3,14,2,15,1)

In cell B2 you can enter this formula if the finishing positions start in cell A2. SWITCH is available in recent versions of Excel. An alternative could be a lookup table or a nested IF formula.

[#3]

@OliverScheurich 

Excellent! Many thanks Oliver.