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

d.g.dixit

Posted 2010-09-08T00:06:04.743

Reputation: 11

1Which spreadsheet program, excel? – Lance Roberts – 2010-09-08T00:09:36.917

OK, I just assumed Excel. – Lance Roberts – 2010-09-08T00:18:47.063

Answers

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

wilson

Posted 2010-09-08T00:06:04.743

Reputation: 4 113

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 – 2010-09-08T13:35:23.400

@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 – 2010-09-09T02:09:43.307

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 – 2010-09-09T14:07:20.570

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

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

Lance Roberts

Posted 2010-09-08T00:06:04.743

Reputation: 7 895

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.

Sux2Lose

Posted 2010-09-08T00:06:04.743

Reputation: 2 962