vlookup: lookup_value as date range

Question

Following Situation:

Sheet 1 provides a date range in two cells:

Beginning____End

1.2013______2.2013

2.2013______3.2013...

Sheet 2 provides specific dates and values

3.1.2013_____500$

5.2.2013_____700$...

What I want to do now is insert the Values from Sheet 2 next to the right date ranges in Sheet 1. Please note that there is only one date (and thus one value) for each date range.

1.2013___ 2.2013___500$

2.2013___ 3.2013___700$

Usually in such a situation I would use Vlookup and match the dates, but that doesn't work since I would have to put a value range as lookup_value.

I would be very thankful for your help. This problem seems so easy but I can't seem to figure out a solution! I tried to apply some solutions for similar problems I found here and on other websites but I haven't found a solution yet.

Thanks and best regards!

Answer 1

I'm not sure whether there's an alternative, but this is what I came up with:

=IF(VLOOKUP(C3,Sheet1!$B$3:$C$11,1)>=B3,VLOOKUP(C3,Sheet1!$B$3:$C$11,2),0)

First thing to note is that you had a 'floating' or unanchored range, which caused your LOOKUP() to work somewhat, but which would ultimately lead to a lot of wrong values.

To the formula, here's a little description:

VLOOKUP(C4,Sheet1!$B$3:$C$11,1)>=B4 this checks if the End of Period is equal to or before the date in Sheet1 we're looking for. In case of month skipping, this bit is what takes care of it.

VLOOKUP(C3,Sheet1!$B$3:$C$11,2) this looks for the corresponding value of the date.

And I updated your file here.

Answer 2

As long as your dates on Sheet2 are in ascending order, you can still use VLOOKUP. If you check the help for VLOOKUP you'll see that if the range_lookup parameter is omitted or TRUE, the function will match the first value that is equal to the search value, or the largest value that is less than the search value. So in your example, if cell B2 on Sheet1 contains 2.2013 then VLOOKUP(Sheet1!B2, Sheet2!$A$1:$B2) will return 500, because 3.1.2013 is the last value in the search column that's less than the search value (Excel treats a month.year date value as equal to 1.month.year).

If there is always a value on Sheet2 for every month - i.e. it never skips from 5.2.2013 to 7.4.2013 for example - then VLOOKUP on its own should do what you need. If there are sometimes missing months on Sheet2 you'll need to limit the search range of the VLOOKUP function to prevent (in this example) the end date 4.2013 from returning the 5.2.2013 value. You can do this using the OFFSET and MATCH functions to construct a range for VLOOKUP's table_array that starts at the beginning of the date range you're looking for. Use a match_type parameter of 1 in the MATCH function to make it behave in the same way as VLOOKUP.