Index and Match
 

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!

I did this for you!
 

BillA, Good Morning.

Your explanation was good but it´s complex to imagine what´s wrong with it.

Please, to easier an answer to you, attach your spreadsheet here.
Remember to ZIP your file before attach it here.

I am sure that your answer will come an a quick way.

------------------------
I did an example about what I understood after your explanation.
It´s attached
------------------------

Index and Match
 

The data looks like this
songs, totals, last used, prior to 2013, 1/5/2013, 1/12/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

When I tried this, the INDEX() formula prompted a "circular reference" warning from Excel. This might have something to do with the problem.

Maybe this would work better:
=INDEX($D$1:$BD$1,MATCH(1,D3:BD3))

Index and Match
 

Maybe this would work better:
=INDEX($D$1:$BD$1,MATCH(1,D3:BD3))

Better yet:
=INDEX($D$1:$BD$1,MATCH(2,D3:BD3))

Thanks! I've got to study your formula to understand it completely, but it does seem to work. You understood the columns correctly in your example. Thanks again for your effort!

If there's no cell with a one in it in the entire row, you do get a circular reference warning. Which is why I put the column where every row had a 1 in it that was labeled "prior to 2013". It keeps the circular reference away.... Maybe a hack, but it seemed to work.

Thanks for the help everyone! I truly appreciate it.

What I ended up find that worked was
=INDEX($1:$1,MATCH(9.99999999999999E+307,2:2))

For comparison, I had been trying
=INDEX($1:$1,MATCH(1,2:2))


So for Song A (the first song) which is in row 2 (the titles are in row 1) if I use the above formula, it works. I don't know why searching for a very large number, vs searching for a 1 works.... but it does.

Thanks for the help!

BillA, Good Morning.

I don´t understand why you need use this formula that you mentioned.

My formula works well BUT the formula that zvkmpw suggested is BETTER and EASIER to understand than mine. It´s brilliant!
YOU can use it easily.
I applied this formula to my example and I sending to you again.

It sounds that your real worksheet have a different layout that I understood.
Please, zip your worksheet and attach it here.
It will be possible to analyse and discover what is the real problem.

Have a nice day.