# check of date with functions lookup

i have input date in sheet1 say in A! 15/07/2010 and in another sheet range of dates with interval of six months in a1 01/01/2010 and in b1 30/06/2010 and c1 15 likewise data is there

i want to check date in a1 with date range of sheet2 and return rescpective value in c column how to achive it using lookup and if functions

-
Which spreadsheet program, excel? – Lance Roberts Sep 8 '10 at 0:09
OK, I just assumed Excel. – Lance Roberts Sep 8 '10 at 0:18

This should do it (written to give default value of zero):

``````=IF(A1>Sheet2!A1,IF(A1<Sheet2!B1,Sheet2!C1,0),0)
``````
-

To my understanding, you mean there are many date ranges in different rows of Sheet2.

Sheet2 should look something like this.

``````  |     A      |      B       |  C
--+------------+--------------+------
1 |  1/1/2010  |   30/6/2010  |  15
2 |  1/7/2010  |  31/12/2010  |  25
3 |  1/1/2011  |   30/6/2011  |  35
4 |  1/7/2011  |  31/12/2011  |  45
``````

As you mentioned, the date ranges are of 6-month interval, you can do this in Sheet1.

``````  |      A      |              B
--+-------------+-------------------------------
1 |  15/7/2010  |  =VLOOKUP(A1,Sheet2!A1:C4,3)
``````

In this solution, `Sheet2!B:B` is NOT referenced. `Sheet1!B1` is actually looking up in `Sheet2!A:A` for the value just smaller than `Sheet1!A1` (15/7/2010). As 1/1/2010 is just smaller than 15/7/2010, the result is 25.

Note: Sheet2 must be sorted for the `VLOOKUP` function to work properly.
Reference: http://www.techonthenet.com/excel/formulas/vlookup.php

-
I think it's a dangerous assumption that the data can, and will always be sorted a particular way. I think the OP should avoid this answer, even though it works and is a little cleaner looking. – Sux2Lose Sep 8 '10 at 13:35
@Sux2Lose, I made this assumption because the author mentioned "range of dates with interval of six months" with first range 1/1/2010 - 30/6/2010 and so on. Maybe we have to think of another answer if this assumption is not correct. – wilson Sep 9 '10 at 2:09
Even if it is a correct assumption, I can envision a user sorting by column C and then your formula is broken. I do understand your logic though. I will post my suggested answer. – Sux2Lose Sep 9 '10 at 14:07

Try this:

``````=SUMPRODUCT(--(Sheet2!A:A>=Sheet1!A1),--(Sheet2!B:B<=Sheet1!A1),Sheet2!C:C)
``````

Using wilson's mock up of this example, this formula would go in Sheet1!B1.

-