Google-sheets – Copy subset of rows from one sheet to another, based on value in column

You can use the following formula to get the "frisbee".

Formula

=QUERY(B3:C8; "SELECT C ORDER BY B DESC LIMIT 1")

Explained

The QUERY will take both columns as dataset, but it will only show column C. Furthermore, it will sort column B descendingly (highest first) and the limit option will limit the result dataset to one row.

In this case, there's no need to calculate the highest date first.

Example

I've copied your file and added my solution: FRISBEE

Give row numbers instead of column letters, and separate the ranges by semicolon ; (vertical stacking instead of horizontal).

={Sheet1!1:3; Sheet2!2:7}

This assumes both sheets 1 and 2 have the same number of columns. If this is not the case, you'll need to specify which columns you want:

={Sheet1!A1:Z3; Sheet2!A2:Z7}

To get all rows of another sheet, use a construction with indirect:

=arrayformula({Sheet1!1:4; indirect("Sheet2!1:" & max(row(Sheet2!A:A)))})

Here, "Sheet2!1:" & max(row(Sheet2!A:A)) constructs a string such as "Sheet2!1:1000", with the latter number obtained as the maximum of row numbers.