SOLVED

# Selecting a corresponding value from a range

Copper Contributor

# Selecting a corresponding value from a range

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 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Selecting a corresponding value from a range

=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.

# Re: Selecting a corresponding value from a range

Excellent! Many thanks Oliver.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Selecting a corresponding value from a range

=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.