Formula reference

[Zongear]

Hello,

I am collecting some stats about football results on a sheet and then pulling info from that sheet to create make sheets for each individual player. 

Unfortunately, the formulae that I am using requires me to go through every cell for each player and change the values to match their column in the 'Results' sheet, which is very time consuming. For example, for the player 'Matt', the formula for 'Red games' is =COUNTIF(Results!3:3,"Red") but then to create the same cell for 'Johnny' I have to manually change it to =COUNTIF(Results!4:4,"Red").

Is there a way I can have the formula reference a 'Player number' to do this automatically? I.e. =COUNTIF(Results!"Player number":Player number,"Red") 

Thanks.

[#2 · Accepted solution]

@Zongear 

You can use

=COUNTIF(INDEX(Results!$1:$1000, MATCH($B$1, Results!$A$1:$A$1000, 0), 0), "Red")

[#3]

Thank you!