Calculating Golf Handicap using VLOOKUP or INDEX MATCH

Hi guys

I am trying to arrange a golf tournament and for this I need a leader board and if people score well I would like the spread sheet to calculate their new handicaps according to the scheme to the right.

All I want to write manually is the start handicap N2 at the beginning and then enter the points for each round in C4.

The goal is that D5 will be calculated automatically in regards to the scheme on the right. So far I have tried some very intrigate IF's and AND's but I can't seem to get it to work.

D5 should first check in which interval in the scheme C5 is. Then check how many points is entered in C4 and then subtract the correct amount according to the scheme.

Under player 2 cell D11 I have tried to make a formula and it works but it only has the interval from 4,5-11,4 in it.

I wonder if any of you could help me.

I look forward to hearing from you.

Sounds like you need to adapt the table slightly to make it usable in an INDEX MATCH query, but could you please explain in simple terms here how the handicap is calculated?

PS I suggest you change your title to "Calculating Golf Handicap using VLOOKUP or INDEX MATCH".

Thanks for changing the title. If you can now please explain the handicap calculation, I can probably give you a formula for it.

One more thing: how is the interval calculated?

Sorry for the questions: I don't play golf!

Originally Posted by RasmusV

That's alright The intervals are not calculated. They are defined by the Danish Golf Association.

No - you don't understand. How do I know which interval range the player falls into? Is it to do with the number of extra strokes? In other words, how do I know which row of the table to use? I understand how to determine the column.

I think this probably does what you want (I've put the formula into D5 and D11):

=IF(C4<$Q$4,C5,C5-INDEX($Q$5:$AE$10,MATCH(C6,$P$5:$P$10,1),MATCH(C4,$Q$4:$AE$4,0)))

It may not work in your file, so try the attachment instead. There was some data in your original which had a leading or trailing space, forcing the formula to malfunction.

In that case, you need this instead (small change in red):

=IF(C4<$Q$4,C5,C5-INDEX($Q$5:$AE$10,MATCH(C5,$P$5:$P$10,1),MATCH(C4,$Q$4:$AE$4,0)))

You need to recreate the player score cards: some of the data is causing a #VALUE! error, and I can't quite determine why, but the formula itself works.

You also need to re-jig your lookup table to match mine: the formula will not work in your own version as it stands.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You're welcome! You can use the reputation button to express your thanks, if you wish.