I've forgotten how to do Index & Match

[SteveP29]

Hi All,
As the title suggests, I've been unable to get and Index & Match formula to work.

I have it successfully for other parts of the sheet I'm working on.

What it is, is I'm setting up a golf competition, Matchplay with players with a higher handicap than their opponent being given 3/4 of the difference between their handicaps.
I have 4 tables to calculate the shots given, table 1 is a grid that calculates the difference between the players handicaps, table 2 is the same grid which then calculates 3/4 of the difference, table 3 is again, the same table but rounding down the difference to a whole number, table 4 does an if calculation to only show a number if the difference is greater than 0

Shots Table

Spain 2026.xlsx
	Q	R	S	T	U	V	W	X	Y
12		Player 1	Player 2	Player 3	Player 4	Player 5	Player 6	Player 7	Player 8
13	Player 1	0	1	0	0	0	0	3	0
14	Player 2	0	0	0	0	0	0	1	0
15	Player 3	6	7	0	5	6	3	9	0
16	Player 4	0	2	0	0	1	0	3	0
17	Player 5	0	0	0	0	0	0	2	0
18	Player 6	3	4	0	2	3	0	6	0
19	Player 7	0	0	0	0	0	0	0	0
20	Player 8	9	10	3	8	9	6	12	0
Handicaps

Cell Formulas
Range	Formula
R12:Y12,Q13:Q20	R12	=H2
R13:Y20	R13	=IF(H13<0,0,H13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S13:Y13,R14,T14:Y14,R15:S15,U15:Y15,R16:T16,V16:Y16,R17:U17,W17:Y17,R18:V18,X18:Y18,R19:W19,Y19,R20:X20	Cell Value	=0	text	NO

On the scoring table, I have everything set up but the number of shots a player will get in their match against their opponent (the yellow shaded box is where the Index/ Match formula needs to go.

Scoring table

Spain 2026.xlsx
	A	B	C	D
10	Match No.		Player	Shots
11	5	TEAM A	Player 1
20
21			TEAM A
22			TEAM B
24
27		TEAM B	Player 2
Singles LO (1)

Cell Formulas
Range	Formula
C11	C11	=Lineups!D25
C27	C27	=Lineups!K25

Whatever I've tried, it never works, any ideas anyone?
Thanks in advance

 

[Snakehips]

@SteveP29 Try like this.
In D11:

=INDEX(Handicaps!$Q$13:$Y$21,MATCH(C11,Handicaps!$Q$13:$Q$21,0),MATCH(C27,Handicaps!$Q$13:$Y$13,0))

In D27:

=INDEX(Handicaps!$Q$13:$Y$21,MATCH(C27,Handicaps!$Q$13:$Q$21,0),MATCH(C11,Handicaps!$Q$13:$Y$13,0))

HTH

 

[SteveP29]

@SteveP29 Try like this.
In D11:

=INDEX(Handicaps!$Q$13:$Y$21,MATCH(C11,Handicaps!$Q$13:$Q$21,0),MATCH(C27,Handicaps!$Q$13:$Y$13,0))

In D27:

=INDEX(Handicaps!$Q$13:$Y$21,MATCH(C27,Handicaps!$Q$13:$Q$21,0),MATCH(C11,Handicaps!$Q$13:$Y$13,0))

HTH

Thank you, just to point out your solution gave the #n/a error, changing the ranges from Q13 to 12, Y21, to 20 and Y13 to 12 worked perfectly.

Thanks again