Vlookup on Dates
 

I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

Vlookup on Dates
 

Try it with the start dates instead:

=VLOOKUP(sought_item,A:C,3)

Hope this helps.

Pete

On Jul 23, 3:49*pm, LossManiac
wrote:
I have a table containg start dates in column a and end dates in column b.. *
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

Vlookup on Dates
 

On Jul 23, 10:49*am, LossManiac
wrote:
I have a table containg start dates in column a and end dates in column b.. *
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

will the target date always be a start or end date? or can it be in
between the start and end date stated on the same row?

Vlookup on Dates
 

LossManiac,

You don't really need to use the VLOOKUP/HLOOKUP functions as these use
exact lookup values to find the required results.

Try using MATCH and INDEX functions instead. for example:

Table1_Random Data:

A B C D E F
1 Start Date End Date Value1 Value2 Value3 Value4
2 01/01/2008 31/01/2008 5123.14 3283.99 4408.01 9272.00
3 01/02/2008 29/02/2008 4941.06 8246.80 686.08 2346.19
4 01/03/2008 31/03/2008 4805.35 4517.27 371.94 7882.13
5 01/04/2008 30/04/2008 9623.49 3801.30 5356.40 9255.24


For the Search date results, I would search on the 'StartDate' Column using
the following formula:

=INDEX(A2:F5,MATCH(Search_Value_Cell_Ref,A2:A5,1), Return_Value_Column_No)

Now the explanation:

The Match Function searches within the given array to determine the Row
position of the largest value that is less than or equal to the
search_value_cell_ref.

It is embeded within the INDEX formula which then uses that value to display
the value in the Return_Value_Column_No within the whole table. So for Value1
I would refer to column 3, Value2 - Column 4, etc.

I think this will help.
--
GKM mcp2000


"LossManiac" wrote:

I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

Vlookup on Dates
 

Edit:

The Match formula in the explanation

MATCH(Search_Value_Cell_Ref,A2:A5,1)

Should read :

MATCH(Search_Value_Cell_Ref,A2:B5,1)

you may have a date that is at the end of the month!!!
--
GKM mcp2000


"Graeme K Moore" wrote:

LossManiac,

You don't really need to use the VLOOKUP/HLOOKUP functions as these use
exact lookup values to find the required results.

Try using MATCH and INDEX functions instead. for example:

Table1_Random Data:

A B C D E F
1 Start Date End Date Value1 Value2 Value3 Value4
2 01/01/2008 31/01/2008 5123.14 3283.99 4408.01 9272.00
3 01/02/2008 29/02/2008 4941.06 8246.80 686.08 2346.19
4 01/03/2008 31/03/2008 4805.35 4517.27 371.94 7882.13
5 01/04/2008 30/04/2008 9623.49 3801.30 5356.40 9255.24


For the Search date results, I would search on the 'StartDate' Column using
the following formula:

=INDEX(A2:F5,MATCH(Search_Value_Cell_Ref,A2:A5,1), Return_Value_Column_No)

Now the explanation:

The Match Function searches within the given array to determine the Row
position of the largest value that is less than or equal to the
search_value_cell_ref.

It is embeded within the INDEX formula which then uses that value to display
the value in the Return_Value_Column_No within the whole table. So for Value1
I would refer to column 3, Value2 - Column 4, etc.

I think this will help.
--
GKM mcp2000


"LossManiac" wrote:

I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

Vlookup on Dates
 

Ignore the above post---- MATCH lookup array can only be 1 dimensional!!
--
GKM mcp2000


"Graeme K Moore" wrote:

Edit:

The Match formula in the explanation

MATCH(Search_Value_Cell_Ref,A2:A5,1)

Should read :

MATCH(Search_Value_Cell_Ref,A2:B5,1)

you may have a date that is at the end of the month!!!
--
GKM mcp2000


"Graeme K Moore" wrote:

LossManiac,

You don't really need to use the VLOOKUP/HLOOKUP functions as these use
exact lookup values to find the required results.

Try using MATCH and INDEX functions instead. for example:

Table1_Random Data:

A B C D E F
1 Start Date End Date Value1 Value2 Value3 Value4
2 01/01/2008 31/01/2008 5123.14 3283.99 4408.01 9272.00
3 01/02/2008 29/02/2008 4941.06 8246.80 686.08 2346.19
4 01/03/2008 31/03/2008 4805.35 4517.27 371.94 7882.13
5 01/04/2008 30/04/2008 9623.49 3801.30 5356.40 9255.24


For the Search date results, I would search on the 'StartDate' Column using
the following formula:

=INDEX(A2:F5,MATCH(Search_Value_Cell_Ref,A2:A5,1), Return_Value_Column_No)

Now the explanation:

The Match Function searches within the given array to determine the Row
position of the largest value that is less than or equal to the
search_value_cell_ref.

It is embeded within the INDEX formula which then uses that value to display
the value in the Return_Value_Column_No within the whole table. So for Value1
I would refer to column 3, Value2 - Column 4, etc.

I think this will help.
--
GKM mcp2000


"LossManiac" wrote:

I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.

Vlookup on Dates
 

Thanks for all of your responses. I have not been able to respond earlier as
the hot link sent to my by Microsoft would not connect me to your replies.

If anyone knows why a link sent to my e mail address is not connecting to
the discussion page I will be most gratefull. I have reported the matter to
Microsoft who say it is a problem but do not produce a solution.

In answer to questions. The problem is that a series of date periods, which
do not follow the callendar year, have each been allocated a year code. The
objective is to allocate a year code to every individual date that appears in
the main database, (currently nearly 6,000 rows long). A date could be any
date within the full range of dates commencing with the with the first date
in the initial date period and ending with the last date in the final date
period.

The lookup table containing the dates is required to show the start and end
date of every date range period for the purposes of reporting the period code
for each date range.

"LossManiac" wrote:

I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.

How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).

Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.

I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.

Thanks for any help/advise.