Originally Posted by BillA

I think this should be simple, but I'm sure am struggling with it. So hopefully someone here can help.
I have a spreadsheet that I'm trying to track the songs our band plays at church and how often they are used.
Column A is the song title
Column B is the total number of times it's been played (this works)
Column C is the date it was last used (this doesn't work)
Column D is a special column that says labeled "prior to 2013" and every cell has a 1 in it.
Column E to BD have the days of the worship service (every seven days) in row 1. Then I place a one in the intersection of the song title and then date.
The formula I'm using in column C is =INDEX($1:$1,MATCH(1,3:3)) [this is the formula for row 3.] My hope was this would find the last item in that row with a 1, then return the date (in row 1 at the top).
The data looks like this
songs, totals, last used, prior to 2013, 1/5/2013, 1/12/2013, 1/19/2013.....
title1, =SUM(E2:BD2), =INDEX($1:$1,MATCH(1,2:2)), 1, 1, , , , 1, ....
title2, =SUM(E3:BD3), =INDEX($1:$1,MATCH(1,3:3)), 1, , , ,1, , , ....
etc
The weirdness it the index/match function works sometimes then quits. It works early in the spreadsheet (like the first 3 months or so). Then it only works if the song is played consecutive weeks, so I have a 1 in consecutive cells. As soon as I have a blank cell, the last used column doesn't update correctly.
I've deleted all the data in cells, so I don't think I've got blanks or other characters in the spreadsheet. Does this function only work for small data sets?
Thanks for any help!