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.