0

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.

Improve this question

1 Answer 1

Reset to default
0

Use the INDEX MATCH syntax.

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

=INDEX(Sheet1!$A$2:$H$10,1,MATCH(Sheet2!A$1,Sheet1!$A$1:$H$1,0))

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.

Improve this answer
3
  • Of course replace sheet 1 with the original data sheet name etc.
    – ruskind
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 at 16:42
  • yes you are right. i missed that. Using =index(sheet1!$A2:$H2 is indeed the most elegant solution
    – ruskind
    Commented Aug 8, 2014 at 0:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .