Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2010, 04:23 PM
FraserKitchell FraserKitchell is offline Moving a Index/Match function Windows XP Moving a Index/Match function Office 2007
Novice
Moving a Index/Match function
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Smile 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
Attached Files
File Type: xls Moving Index;Match.xls (59.0 KB, 17 views)
Reply With Quote
  #2  
Old 01-19-2010, 12:23 AM
ConneXionLost's Avatar
ConneXionLost ConneXionLost is offline Moving a Index/Match function Windows XP Moving a Index/Match function Office 2003
Simulacrum
 
Join Date: Jan 2010
Location: Victoria, Canada
Posts: 86
ConneXionLost is on a distinguished road
Default

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,
Attached Files
File Type: xls Fraser.xls (112.5 KB, 16 views)
Reply With Quote
  #3  
Old 01-19-2010, 09:38 AM
FraserKitchell FraserKitchell is offline Moving a Index/Match function Windows XP Moving a Index/Match function Office 2007
Novice
Moving a Index/Match function
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
index/match, moving

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving a Index/Match function 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
Moving a Index/Match function 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft