Forum Discussion
End of quarter data
Let's say your data are on a sheet named Data, starting in row 3, in columns A to K.
If you have Microsoft 365 or Office 2021:
On another sheet, enter this formula in A2:
=EDATE(DATE(2006,12,31),SEQUENCE(29,,0,3))
DATE(2006,12,31) is the date of the first end-of-quarter that you need (31-12-2006).
In B2, enter the formula
=XLOOKUP(A2,Data!$A$3:$A$3000,Data!$B$3:$K$3000,"",-1)
Fill down to the last used row.
If you have an older version:
In A2 and A3, enter the first two end-of quarter dates, for example 31-12-2006 and 31-03-2007.
Select A2 and A3, then use the fill handle in the lower right corner of A3 to fill down until you the end-of-quarter dates for 7 years.
In B2, enter the formula
=VLOOKUP($A2,Data!$A$3:$K$4000,COLUMN())
Fill to the right to K2, then fill down.
Let's say your data are on a sheet named Data, starting in row 3, in columns A to K.
If you have Microsoft 365 or Office 2021:
On another sheet, enter this formula in A2:
=EDATE(DATE(2006,12,31),SEQUENCE(29,,0,3))
DATE(2006,12,31) is the date of the first end-of-quarter that you need (31-12-2006).
In B2, enter the formula
=XLOOKUP(A2,Data!$A$3:$A$3000,Data!$B$3:$K$3000,"",-1)
Fill down to the last used row.
If you have an older version:
In A2 and A3, enter the first two end-of quarter dates, for example 31-12-2006 and 31-03-2007.
Select A2 and A3, then use the fill handle in the lower right corner of A3 to fill down until you the end-of-quarter dates for 7 years.
In B2, enter the formula
=VLOOKUP($A2,Data!$A$3:$K$4000,COLUMN())
Fill to the right to K2, then fill down.
- Thank you so much 🙂
