Google Sheet: Automatically Populate Table with Multiple Different Cell Values Associated with Most Recent Date In Another Table Using Formulas

Question

I have the following sample table in the exact layout below inside Google Sheets. Please note the actual table has a lot more rows and dates so this is just a simple example.

		Tom	Jim
11/27/2023	value 1	42	70
	target	33	65
12/4/2023	value 1	37	66
	target	29	54

I have another table that I want to automatically populate every time I update my first table with additional rows of data. Specifically, I want to populate the new table with value1 and target from the most recent date of the first table. So in the above example I want my output table to look like this:

Metrics	Tom	Jim
value 1	37	66
target	29	54

The most recent date is 12/4/2023 and I want its corresponding values for Tom and Jim.

I have tried playing around with filter() and max() formulas but they don't end up populating the cells correctly.

What Google Sheet formula should I use to achieve this?

Answer 1

You may try: (also this assumes that dates in Column_A are always goin' to be in ascending order)

=xlookup($F2,$B:$B,C:C,,,-1)