I have a spreadsheet that contains a bunch of dates with several rows of data beneath the cells containing the dates (new dates & data are appended to the spreadsheet on a weekly basis)

enter image description here

In another spreadsheet I have 7 columns containing the dates of the last 7 days (including the current day). This date information is acquired using "=TODAY()".

enter image description here

How can I search the data spreadsheet (first screenshot) for cells matching the dates in the date spreadsheet and then copy the 10 cells below the matching date cell to the corresponding cells in the date spreadsheet? The end result would be something similar to the following:

enter image description here

The dates spreadsheet would update dynamically, so for instance when the current date changes the data in the dates spreadsheet would update to match the new selection of dates.


Use the INDEX MATCH syntax.

for the cell A2 in the sheet with the live dates in, enter


BUT replace H with the final column in which the original data extends to, and 10 with the final row the data extends to (Both in all instances in the formula). If you then fill down the formula and fill across the formula you should get the desired result.

Feel free to ask if it doesn't work/anymore questions.

  • Of course replace sheet 1 with the original data sheet name etc. – duhamp Aug 7 '14 at 14:56
  • In the index function your row variable (1) needs to update. This could either be done manually or using the row() function to adjust it as it is dragged down. Alternatively in the index array don't use all the rows just use something like =index(sheet1!$A2:$H2...) and it will reference the different rows – gtwebb Aug 7 '14 at 16:42
  • yes you are right. i missed that. Using =index(sheet1!$A2:$H2 is indeed the most elegant solution – duhamp Aug 8 '14 at 0:07

Your Answer

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

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