Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest date or matching
I am having trouble finding a solution to the following;
I have a dates located in the G column on a worksheet named JobSheet, from another worksheet (Sales Order) I am copying a date from a cell then need to find the a date on the JobSheet that matches the Sales order date or the closest one to it. If it finds a matching date I need to go to the last entry with that date and compare a second field in that row with a variable on the Sales Order. If the date cannot be matched I just need to find the closest date prior to the one on the Sales Order and go to the end of that date range. I cannot use VLOOKUP to find these values since my dates are in the right most column of the worksheet and cannot be changed. I have most of this coded, variables and outside routines, I just need a method of searching < or = the date specified on the Sales Order. Any input is much appreciated. Thanks, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest date or matching
using a combination of Index and Match will give you the same functionality
of Vlookup without the limitation of looking in the leftmost column Index(columnN,Match(value,columnT,1),1) as a "pseudo code" example. See the help on Match for details on its behavior and requirements for inexact matches. -- Regards, Tom Ogilvy " wrote: I am having trouble finding a solution to the following; I have a dates located in the G column on a worksheet named JobSheet, from another worksheet (Sales Order) I am copying a date from a cell then need to find the a date on the JobSheet that matches the Sales order date or the closest one to it. If it finds a matching date I need to go to the last entry with that date and compare a second field in that row with a variable on the Sales Order. If the date cannot be matched I just need to find the closest date prior to the one on the Sales Order and go to the end of that date range. I cannot use VLOOKUP to find these values since my dates are in the right most column of the worksheet and cannot be changed. I have most of this coded, variables and outside routines, I just need a method of searching < or = the date specified on the Sales Order. Any input is much appreciated. Thanks, Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest date or matching
Tom,
Which is faster, vlookup or the Index Match combo? "Tom Ogilvy" wrote in message ... using a combination of Index and Match will give you the same functionality of Vlookup without the limitation of looking in the leftmost column Index(columnN,Match(value,columnT,1),1) as a "pseudo code" example. See the help on Match for details on its behavior and requirements for inexact matches. -- Regards, Tom Ogilvy " wrote: I am having trouble finding a solution to the following; I have a dates located in the G column on a worksheet named JobSheet, from another worksheet (Sales Order) I am copying a date from a cell then need to find the a date on the JobSheet that matches the Sales order date or the closest one to it. If it finds a matching date I need to go to the last entry with that date and compare a second field in that row with a variable on the Sales Order. If the date cannot be matched I just need to find the closest date prior to the one on the Sales Order and go to the end of that date range. I cannot use VLOOKUP to find these values since my dates are in the right most column of the worksheet and cannot be changed. I have most of this coded, variables and outside routines, I just need a method of searching < or = the date specified on the Sales Order. Any input is much appreciated. Thanks, Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest date or matching
http://www.decisionmodels.com
would have information like that. -- Regards, Tom Ogilvy "Dave" wrote: Tom, Which is faster, vlookup or the Index Match combo? "Tom Ogilvy" wrote in message ... using a combination of Index and Match will give you the same functionality of Vlookup without the limitation of looking in the leftmost column Index(columnN,Match(value,columnT,1),1) as a "pseudo code" example. See the help on Match for details on its behavior and requirements for inexact matches. -- Regards, Tom Ogilvy " wrote: I am having trouble finding a solution to the following; I have a dates located in the G column on a worksheet named JobSheet, from another worksheet (Sales Order) I am copying a date from a cell then need to find the a date on the JobSheet that matches the Sales order date or the closest one to it. If it finds a matching date I need to go to the last entry with that date and compare a second field in that row with a variable on the Sales Order. If the date cannot be matched I just need to find the closest date prior to the one on the Sales Order and go to the end of that date range. I cannot use VLOOKUP to find these values since my dates are in the right most column of the worksheet and cannot be changed. I have most of this coded, variables and outside routines, I just need a method of searching < or = the date specified on the Sales Order. Any input is much appreciated. Thanks, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the date closest to today. | Excel Discussion (Misc queries) | |||
Find matching date in another worksheet, copy and paste data | Excel Discussion (Misc queries) | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
Find closest future date from list ... | Excel Discussion (Misc queries) | |||
Macro to find matching date and copy values to another sheet | Excel Discussion (Misc queries) |