Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I’m trying to get a cell to change its value based on a date range.

Let's say I have this list:

A        B
date1    10
date2    25
date3    50
date4    85

On date1 I will have 10 items, date2 I will have 25 etc.

What I’m trying to do is if today's date is after (or on) date1 but before date2, input date1's value from B. Then, once we hit date2, input date2's value from B.

share|improve this question
Can you clarify this? Maybe publish a Google Spreadsheet that shows what you need? You may be able to use nested IF statements. – jaredcohe Nov 10 '13 at 1:42
Not sure what I can provide beyond the snippet. – Rob Nov 10 '13 at 3:48
Basically, if I have an input of just today's date, and today's date is between date1 and date2, I need the formula to return "10" (B2). If today's date is between date2 and date3, I need the formula to return "25" (B3). – Rob Nov 10 '13 at 3:51
Are the dates sorted? So that date1 < date2 < date3 < date4? – Vidar S. Ramdal Nov 11 '13 at 8:37

If I've understood the question correctly, you may use something like

=VLOOKUP(MAX(FILTER(A1:A; A1:A <= NOW())); A1:B; 2)

The formula FILTERs the date column, so that only dates <= today considered. The MAX function returns the latest of these dates. Then, the VLOOKUP returns the corresponding value from column B (the 2nd column in the A1:B matrix).

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.