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!!!! :-)
4 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)=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.
=INDEX(C21:C36;MATCH(C48;A21:A36);1)or
=XLOOKUP(C48;A21:A36;C21:C36;;-1;1)
