CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Mary Nottingham
Mary Nottingham

Reputation: 1

Google Sheets - formula to return date value based on range of dates on another page

On Sheet 1 I have a range of dates in columns A & C. On Sheet 2 I have a list of items that include a date of occurrence in column M. I need a formula on Sheet 2 column A that will evaluate that date of occurrence, determine which date range from Sheet 1 this date falls into, and return the value of the starting date range. So, for the images attached, the formula would return the 5/18/24 date in cell A2 on Sheet 2 because the date of occurrence (6/3/24) falls in between 5/18/24 thru 6/14/24. Date Ranges Formula cell

I am struggling to determine the correct formula.

Upvotes: 0

Views: 393

Answers (1)

Andrew Merrill
Andrew Merrill

Reputation: 1752

If you don't have any overlapping date ranges, and every occurrence date is guaranteed to be in one of the provided ranges, then the xlookup function will do this easily. The goal is to find the closest value in column A (on Sheet 1) that is equal to or less than the occurrence date in column M (on Sheet 2).

If you haven't used xlookup before, its arguments are:

  1. The item to search for (that's your occurrence date)
  2. The range (a row or column) to search through (that's your start dates)
  3. The range (a row or column) with the result (that's also your start dates)
  4. a "missing value" to use in case the search fails
  5. The match mode (0 for exact match, 1 for closest bigger value, -1 for closest smaller value) so you want -1 here
  6. The search mode (which direction to search, and whether to use a fast binary search if your data is ordered) which you can probably ignore

So, a formula like: =xlookup(M2, Sheet1!A:A, Sheet1!A:A, "no match found", -1) in cell A2 on Sheet2 should be a good place to start.

Upvotes: 1

Related Questions