Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! Last edited by BillA : May 3rd 13 at 05:01 PM Reason: typo |
#2
|
|||
|
|||
I did this for you!
Quote:
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 ------------------------
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil Last edited by Mazzaropi : May 4th 13 at 03:20 PM Reason: Including an example as help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#5
|
|||
|
|||
Quote:
|
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
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! |
#8
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX MATCH Functions in LibreCalc (Excel) - Non unique valuesreturn wrong index result | Excel Worksheet Functions | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |