Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

You can use the VLOOKUP function in Google Sheets to retrieve the closest date-matching value. Here are the steps:

  1. Sort your data in ascending order by the date column.
  2. In a new column, use the ABS function to calculate the absolute difference between the date you want to match and each date in your data set.
  3. Use the MIN function to find the smallest absolute difference.
  4. Use the INDEX and MATCH functions together with VLOOKUP to retrieve the value from the column that corresponds to the closest date match.

Here's an example formula:

=VLOOKUP(INDEX(daterange, MATCH(MIN(abs(daterange-datetomatch)), abs(daterange-datetomatch), 0)), rangetosearch, columnindex, FALSE)

In this formula, daterange is the range of dates in your data set, datetomatch is the date that you want to find the closest match for, rangetosearch is the range of data you want to retrieve the value from, and columnindex is the index number of the column in the rangetosearch that contains the value you want to retrieve. The FALSE argument in the VLOOKUP function ensures that an exact match is not required.