How do you import a range based off a common cell value/date across 2 entire sheets

Question

I am looking to import a column/data based off a common date.

Currently using =TODAY()+2 in "Sheet A" to get the date. I have another sheet ("Sheet B"), which has data that is updated every day automatically, with a row of dates.

Data which is updated daily

I want my spreadsheet to import the column of data in "Sheet B" based on the date generated by =TODAY()+2 in "Sheet A".

For reference, the date I would want today would be in 'DL7' in "Sheet B". And the cell =TODAY()+2 is in is 'O1' in "Sheet A".

Answer 1

You can use FILTER() to get the data column in "SheetB" based on the date value in "SheetA"

Sample:

SheetB:

SheetA:

Formula:

SheetA A1: =today()+2
SheetA B1: =filter(SheetB!A2:AD,SheetB!A2:AD2=A1)

What it does?

• I provided the range of data to be filtered which is SheetB!A2:AD
• I want to filter the data based on its date that matched the value in SheetA A1, Hence the condition would be SheetB!A2:AD2=A1

---

(Update)

If SheetA and SheetB is in a separate Google Sheet. You can use this formula in SheetA B1:

=filter(importrange("https://docs.google.com/spreadsheets/d/1RPQ7WOTOZZSirf60kP6nULhViCdsuGHcOxxxxx/edit","SheetB!A2:AD"),index(importrange("https://docs.google.com/spreadsheets/d/1RPQ7WOTOZZSirf60kP6nULhViCdsuGHcOxxxxx/edit","SheetB!A2:AD"),1,0)=A1)

What it does?

1. We used IMPORTRANGE() to import our data from another Google Sheet
2. We used INDEX() to get our imported data's row 1 values. Which will be used as a criteria for our FILTER()

Output: