Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

In Google Sheets, how do I pad a table of dated values with dates where nothing changed?

I’ve got a table with data like this showing chronological dates when a balance value changed:

----Table 1----
Date    Balance
1/1/19  0
1/3/19  2,000.00
1/6/19  2,159.81

I also added an adjacent column of calendar dates that has every single day on the calendar listed. I’m trying to come up with a formula for an additional "Balance" column that somehow uses the data from my first table to automatically "fill in" the balance values of my new table, repeating the values when they don’t change:

----Table 2----
Date    Balance
1/1/19  0
1/2/19  0
1/3/19  2,000.00
1/4/19  2,000.00
1/5/19  2,000.00
1/6/19  2,159.81
1/7/19  2,159.81
1/8/19  2,159.81

I’ve tried various things like =MATCH and =VLOOKUP but nothing I try is coming close to making this work, and I am worried I will have to enter these manually. Is there a formula that can do this automatically?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Within Sheets you may try:

=index(xlookup(D2:D,A:A,B:B,,-1))

enter image description here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading