Formula to lookup values in specific cells based on values in other cells in the same column

[Rnkhch]

Hello,

I have a large table that looks like this:
​

(week)	1	1	1	1	1	2	2	2	2	2	etc
(day)	1	2	3	4	5	1	2	3	4	5	etc
Person1	78	89	91	93	95	93	87	92	90	81	etc
Person2	81	88	77	73	72	84	86	89	85	93	etc
etc

<tbody>
</tbody>

I would like to define a formula that calculates "weekly score improvement" for each person, by subtracting score of week1/day5 from week1/day1 and repeating this for all subsequent weeks, i.e. for instance:

Person1's improvement in week1 = (the score in week1/day1 column) - (the score in week1/day5 column)

How can I use the week and day identifiers to call values from those columns, for each person?

Any input is highly appreciated! Thanks!​

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	(week)	1​	1​	1​	1​	1​	2​	2​	2​	2​	2​
2​	(day)	1​	2​	3​	4​	5​	1​	2​	3​	4​	5​
3​	Person1	78​	89​	91​	93​	95​	93​	87​	92​	90​	81​
4​	Person2	81​	88​	77​	73​	72​	84​	86​	89​	85​	93​
5​
6​		1​	2​
7​	Person1	-17​	12​
8​	Person2	9​	-9​
9​

Sheet: Sheet14

Formula in cell B7:
=INDEX($B$3:$K$4, MATCH($A7,$A$3:$A$4,0),MATCH(B$6,$B$1:$K$1,0))-INDEX($B$3:$K$4, MATCH($A7,$A$3:$A$4,0),MATCH(B$6,$B$1:$K$1,0)+4)

 

[Rnkhch]

Thank you so much! This is an excellent formula Just one thing I noticed is that some weeks may have less than 5 days (because of holidays), so the "+4" in the formula would not be able to take care of those. I was thinking to use a "MAX" formula instead of 4 to figure out the maximum number of days in the week and add "MAX-1" instead of 4, is that right? If you could help me add this capability to the formula, I would highly appreciate it. Thanks a lot!

 

[Oscar Cronquist]

Thank you!

Try this in cell B7:
=INDEX($B$3:$K$4,MATCH($A7,$A$3:$A$4,0),MATCH(B$6,$B$1:$K$1,0))-INDEX($B$3:$K$4,MATCH($A7,$A$3:$A$4,0),MATCH(B$6,$B$1:$K$1,0)+COUNTIF($B$1:$K$1,B$6)-1)

 

[Rnkhch]

Thank you very much! I'm gonna start applying this to my big data file. This formula will make my scoring process so much easier and faster!

I may need to do some other calculations with my scores as well, which I'll post

Awesome forum