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

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

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

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