Formula to search for today's date and return range of data

Hello. I could really use help writing a formula:

I want to search for today's date in one worksheet, let's call it Sheet2. When today's date is found, I want a range of data to the right of today's date to be placed/referenced/pasted into another worksheet (the worksheet where the formula is written, or Sheet1). The data range, for example, would be the 5 cells to the right of today's date, and 6 cells down (total of 30 cells) - the range is relative to the date (think of a work schedule). Every day Sheet1 would update with the current day's information/schedule because it searches for today's date.

Is there a way to accomplish this? Thank you in advance!

You can use Index/Match

e.g.

=INDEX('Sheet2'!B:B,MATCH($A2,'Sheet2'!$A:$A,0))

and copy across.

this looks for value in A2, finds it in sheet2, column A, then returns item in column B at that row. Copying across gets from C, D, E, etc...

Can I pull a range of columns and rows over to Sheet1? Would I have to have today's date in each corresponding row in column A?

unless there is a consistent number of rows for each date, then it is best you have a date in each row, but there are workarounds using helper columns... if you post a sample workbook (no confidential info included), then we can review it and make suggestions.

So if you have date in Sheet2, cell A1, then in A2:

=INDEX('Weekly Schedule'!B:B,MATCH($A$1,'Weekly Schedule'!$A:$A,0)+ROWS($A$1:$A2)-2)

copied down 4 rows and across.

Assuming each date has 4 rows of data to pull per your sample

You are quite welcome, can you please mark the thread as Solved?



How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save