5

I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for these missing dates. How to achieve this, preferably with a formula that does not need to be copied down the sheet?

+---+-----------+-------+
|   |     A     |   B   |
+---+-----------+-------+
| 1 | Date      | Value |
| 2 | 3/25/2016 | 25    |
| 3 | 3/26/2016 | 15    |
| 4 | 3/28/2016 | 74    |
+---+-----------+-------+

Desired output:

+---+-----------+-------+
|   |     C     |   D   |
+---+-----------+-------+
| 1 | Date      | Value |
| 2 | 3/25/2016 | 25    |
| 3 | 3/26/2016 | 15    |
| 4 | 3/27/2016 | 0     |
| 5 | 3/28/2016 | 74    |
+---+-----------+-------+
3

I used two arrayformulas for this purpose. One, in cell C2, to create the list of dates:

=arrayformula(if(row(C2:C)+A2-2 > max(A2:A), , row(C2:C)+A2-2))

It adds the row number minus 2 to the date in A2. If the result is greater than the last date in column A, this date is out of range and is not shown.

Then in column D, vlookup is used to locate the value for each date. If it cannot find the exact date (raising an error), 0 is used.

=arrayformula(iferror(vlookup(filter(C2:C, len(C2:C)), filter(A2:B, len(A2:A)), 2, false), 0)) 

The last parameter of vlookup, "sorted" is set to False in order to force exact match, even though the array of dates is actually sorted.

  • With some local language settings you have to use colons ; instead of commas. – Guglie Jan 5 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy