Index and Match

[BillA]

I think this should be simple, but I'm sure am struggling with it. S
hopefully someone here can help

I have a spreadsheet that I'm trying to track the songs our band play
at church and how often they are used
Column A is the song titl
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 ever
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 an
then date

The formula I'm using in column C is =INDEX($1:$1,MATCH(1,3:3)) [this i
the formula for row 3.] My hope was this would find the last item i
that row with a 1, then return the date (in row 1 at the top)

The data looks like thi
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, , , ...
et

The weirdness it the index/match function works sometimes then quits. I
works early in the spreadsheet (like the first 3 months or so). Then i
only works if the song is played consecutive weeks, so I have a 1 i
consecutive cells. As soon as I have a blank cell, the last used colum
doesn't update correctly

I've deleted all the data in cells, so I don't think I've got blanks o
other characters in the spreadsheet. Does this function only work fo
small data sets

Thanks for any help

 

[Mazzaropi]

I think this should be simple, but I'm sure am struggling with it. S
hopefully someone here can help.

I have a spreadsheet that I'm trying to track the songs our band play
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 ever
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 an
then date.

The formula I'm using in column C is =INDEX($1:$1,MATCH(1,3:3)) [this i
the formula for row 3.] My hope was this would find the last item i
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. I
works early in the spreadsheet (like the first 3 months or so). Then i
only works if the song is played consecutive weeks, so I have a 1 i
consecutive cells. As soon as I have a blank cell, the last used colum
doesn't update correctly.

I've deleted all the data in cells, so I don't think I've got blanks o
other characters in the spreadsheet. Does this function only work fo
small data sets?

Thanks for any help!

*BillA*, Good Morning.

Your explanation was good but it´s complex to imagine what´s wrong wit
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

 

[zvkmpw]

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))

 

[zvkmpw]

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))

 

[BillA]

*BillA*, Good Morning.

Your explanation was good but it´s complex to imagine what´s wrong wit
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.

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Mazzaropi]

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 ro
1) if I use the above formula, it works. I don't know why searching fo
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 i
*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
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

+-------------------------------------------------------------------
|Filename: 08_05_2013_ExcelBanter_BillA_Songs_2.0.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=870
+-------------------------------------------------------------------