REFERENCE A COLUMN THAT SHIFTS LEFT EACH WEEK

[JLouis]

Edit: Sorry the title is wrong. I meant shifts to the right.

Good day all!

I'm working on a sports spreadsheet that tracks stats for players each week. The report I get online has stats for each week listed and then average week at the end.

My current problem is that I am only interested in the average per week column which shifts to right after each week by 1. In the table below, the target column is I, TAR/G. Next week, that column will be shifted right 1 column to J, and so on.

How do I look up the player name and corresponding TAR/G data, even when the column shifts to the left? I'd like a formula ideally but a macro would be ok as a second choice.

Thanks for looking!

WR-targets.csv
	A	B	C	D	E	F	G	H	I
1			Week-By-Week Targets						Averages
2	Name	Team	1	2	3	4	5	6	TAR/G
3	Puka Nacua	LAR	15	20	7	10	11	-	12.6
4	Ja'Marr Chase	CIN	9	8	15	9	19	-	12
5	Davante Adams	LV	9	8	20	13	4	-	10.8
6	Justin Jefferson	MIN	12	13	13	9	6	-	10.6
7	A.J. Brown	PHI	10	6	14	13	8	-	10.2
8	Stefon Diggs	BUF	13	7	12	7	11	-	10
WR-targets

 

[JLouis]

I figured it out from another thread on this site.

=IFERROR(VLOOKUP(Z2,$A$3:$J$500,MATCH("TAR/G",$A$3:$J$3,0),0),"")

 

[Peter_SSs]

I figured it out from another thread on this site.

=IFERROR(VLOOKUP(Z2,$A$3:$J$500,MATCH("TAR/G",$A$3:$J$3,0),0),"")

Thanks for letting us know.

For the future though, please note #14 of the Forum Rules in relation to the use of all upper case in your thread titles.

 

[JLouis]

I never knew that. Thanks.