Forum Discussion
Index and match mystery (for me that is...)
Hello,
I am having a hard time with an Index and match function I created. It's very simple but I am overlooking something.
I am creating a file in which I can keep track of my golf progress. In golf you use a handicap system. This means that based on your formal handicap you get a number of extra strokes on a course.
For instance:
The formal handicap of a player is 21.3
Ranges:
| From | to | Course handicap additional strokes |
| 16,4 | 19,4 | 3 |
| 19,5 | 22,5 | 4 |
| 22,6 | 25,7 | 5 |
In this example the player is awarded 4 additional strokes based on that 21.3 falls in the range from 19,5 to 22,5.
I've created a function but it doesn't always seem to work...It does return a value but not always the correct one....
=INDEX(C21:C36;MATCH(C48;A21:A36+(C48<=B21:B36);1))
In cell C48 people can fill in their formal handicap.
In cells A21-A36 the 'from' values of the range
In cells B21-B36 the 'to' values of the range
In cells C21-C36 the extra strokes for the course handicap
What am I doing wrong?
Merry Xmas!!!! :-)
=INDEX(C21:C36;MATCH(C48;A21:A36);1)or
=XLOOKUP(C48;A21:A36;C21:C36;;-1;1)
7 Replies
Or
=XLOOKUP(1, (A21:A48 <= D48) * (B21:B48 >= D48), C21:C48)Or
=LOOKUP(2, 1/($A$21:$A$36<=$C48)/($A$21:$A$36>0), $C$21:$C$36)
Helloeacrusher,You’re very close the issue is that your formula is trying to use `MATCH` in approximate mode against a manipulated array, which doesn’t reliably test whether a value falls between two bounds. The clean way to do this is with a between‑bounds check:
=INDEX(C21:C36, MATCH(1, (C48>=A21:A36)*(C48<=B21:B36), 0))
• C48 → player’s formal handicap
• A21:A36 → “From” values
• B21:B36 → “To” values
• C21:C36 → strokes awarded
This works because `(C48>=A21:A36)*(C48<=B21:B36)` creates an array of 1s and 0s (TRUE/FALSE). `MATCH(1,…,0)` finds the first row where both conditions are TRUE, and `INDEX` returns the strokes from column C.In your example, with handicap 21.3, the formula correctly returns 4 because 21.3 is between 19.5 and 22.5.
Notes:
• In older Excel, confirm with Ctrl+Shift+Enter (array formula). In Microsoft 365, just press Enter.
• Make sure your ranges don’t overlap or leave gaps, and that the numbers are stored as numbers (not text).
• If your ranges are continuous and sorted, you could simplify further with `VLOOKUP` in approximate mode, but the formula above handles explicit From/To ranges directly.=INDEX(C21:C36,MATCH(1,(A21:A36<=D48)*(B21:B36>=D48),0))This formula works in my sample file and in modern and legacy Excel such as Excel 2013. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.
Thank you soo much!
=INDEX(C21:C36;MATCH(C48;A21:A36);1)or
=XLOOKUP(C48;A21:A36;C21:C36;;-1;1)Thanks a lot... :-)
Problem solved. Onwards to my next issue :-)
