Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
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

3 Answers 3

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

share|improve this answer

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.

share|improve this answer
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:


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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.