How to reference a cell with a column number that corresponds to a particular week?

[Rnkhch]

Hello,

I'm trying to make a formula like this in order to refer to scores in my "Scores" sheet form a different sheet:

=INDEX(Scores!$A$1:$AP$55,MATCH($A5,Scores!$A$2:$A$55,0),INDIRECT("Scores!"&"COLUMN((TODAY()-WEEKDAY(TODAY(),2)+5))"))

But I can't get the bold part to work correctly in terms of indicating the column number in the INDEX function. In this example, the formula is trying to refer to the column of the current week using

TODAY()-WEEKDAY(TODAY(),2)+5)​

Thanks for any input!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]08/24/2018[/TD]
[TD]08/17/2018[/TD]
[TD]08/10/2018[/TD]
[TD]08/03/2018[/TD]
[TD]07/27/2018[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]student1[/TD]
[TD]96[/TD]
[TD]87[/TD]
[TD]93[/TD]
[TD]91[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]student2[/TD]
[TD]78[/TD]
[TD]81[/TD]
[TD]84[/TD]
[TD]90[/TD]
[TD]86[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]student3[/TD]
[TD]87[/TD]
[TD]93[/TD]
[TD]90[/TD]
[TD]95[/TD]
[TD]99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

=INDEX(Scores!$A$2:$AP$55,MATCH($A5,Scores!$A$2:$A$55,0),MATCH(TODAY()-WEEKDAY(TODAY(),2)+5,Scores!$A$1:$AP$1,0))

M.

 

[Rnkhch]

Thank you! It works well, but I just realized that if this Friday was a holiday and the scores for this week had Thursday's date (i.e. 08/23/2018), then the formula returns N/A. Is there a way to deal with this? Perhaps matching the "current week" to the "MAX written day of the current week" on the Scores sheet?

Thanks a lot!

 

[JonXL]

I think changing that last 0 in the formula to a 1 will give you the behavior you're after.

 

[Rnkhch]

Just tested, and it doesn't seem to be working. I have a feeling it might be due to the order of the dates which is the opposite of what the MATCH function wants? Any ideas? Thanks a lot!

 

[JonXL]

=INDEX(Scores!$A$2:$AP$55,MATCH($A5,Scores!$A$2:$A$55,0),MATCH((TODAY()-WEEKDAY(TODAY(),2)+5)-4,Scores!$A$1:$AP$1,-1))

This will hopefully work. Since your dates are in descending order, you can use -1 to look for the first match greater than or equal to your lookup value and then reference Monday of the current week instead of Friday.

 

[Rnkhch]

Doesn't seem to be working