#1
|
|||
|
|||
Moving a Index/Match function
I am trying to have an index/match formula that moves to a new array. I am having trouble getting the ROW part of the Index function to move on to a new row at an appropriate time. An example file is attached, because I am having trouble explaining this.
You will see that I have to move the row on the 10th of the month because the data for the 11th is stored in a newer row... Any ideas for how to get that index/match function to be a little smarter? Good luck! Thanks, Fraser |
#2
|
||||
|
||||
Hi Fraser,
INDEX/MATCH works better when the data is in a contiguous block where the row and column indices are in one row and one column respectively. I re-arranged your data, on sheet "Data". You can see the indices highlighted in blue. Note that the dates on row 6 are all on the same row compared to before when you had them on three separate rows. This makes it possible for MATCH to look in one place to find what it needs. Note - the data in pink is now redundant. I didn't delete those columns because I didn't know what you use the header data for. I put your summary list on sheet "Summary" and changed one date (in yellow) where you seemed to be looking for Nov instead of Oct. If you find you don't need some of your header data, you might also want to start using dynamic named ranges for this lookup. It will save you the requirement to change your formula ranges when you add new data. Cheers, |
#3
|
|||
|
|||
Thanks thats a great idea, I'll consider this solved. Dynamic named ranges...I'm gonna need to do a little studying. Thanks for the hand.
|
Tags |
index/match, moving |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I create an index of every word in a document? | jbengal | Word | 5 | 11-23-2022 02:17 PM |
Problem with creating index | klb_08 | Word | 0 | 11-11-2009 06:08 AM |
Match numbered list formatting b/w documents? | sbranecki | Word | 0 | 09-10-2009 03:12 PM |
Problems with Match | Theofficehedgehog | Excel | 3 | 07-26-2009 02:07 PM |
Need another formula to match my last one | tinkertron | Excel | 2 | 04-29-2009 02:17 PM |