check of date with functions lookup

Question

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

Answer 1

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

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

Answer 2

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

Answer 3

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.