Index and Match - my nemesis

[whodey33]

Alright my peoples... This is a formula that just I never can get it right.

I start with this table on sheet 1. Date row on top, room # in first column. Dates run until 6/30/21 Data of G and R will be put in each week for the date (auditing something)

What I'm trying to do on sheet 2 is have a rolling 7 days that will have a calculation % of Green # over 7 along with some other useful info. So that gets into the index and match where the room # column is static. The date row be a formula (e.g. =today()-7). So! I'm trying to make a find and match formula that the index table is on sheet 1 (for all dates/rooms), match date cell and room cell to provide that G or R. Below is a screen shot of it not working correctly. 9/15 has no data, getting other errors, something is wrong and not sure!

Any help would be super duper

 

[Toadstool]

Hi Whodey33,

INDEX uses array then row then column. I think you may have row and column the wrong way round.

 

[whodey33]

Hi Whodey33,

INDEX uses array then row then column. I think you may have row and column the wrong way round.

Good call...I was so focused on the fact I had the row first and column second (which is the way the formula reads) i didn't think about flip flopping to see what happens. A bit confusing however it worked!

Thank you for getting me out of my tunnel vision!

 

[Toadstool]

You're welcome.