Formula for checking a date against a list of start and end dates to return a specific week number

Question

Formula for checking a date against a list of start and end dates to return a specific week number

Hi, I am trying to check a date (Single cell) against a list of start and end dates, so that it returns a week number. See the screenshot attached.

The result expands in a number of cells where I see all the results are 0 and one result is what I'm looking for W43.

I imagine the formula gives me a result for each of the rows of the target list of values. What I want however is to have a formula that only returns the value I need W43, so that I can repeat the search for every Date Created. Anyone has a suggestion? Thank you very much.

[Re-Titled by Moderator]

Original Title: Checking one cell against a list of cells with IF and getting multiple results

MacBook Pro 16″, macOS 15.4

Posted on Jul 22, 2025 03:00 AM

Reply

Answer 1

Badunit

Level 7

29,898 points

Jul 22, 2025 06:03 AM in response to Infinite Loop, 1

Your screenshot either didn't make it into the post or was removed.

If your start dates are in Table 1 column A, end dates in column B, week number (text such as "W43") in column C, Table 1 is sorted ascending by start date, and the "single cell" is Table 2 cell B2 , the formula for cell C2 can be

=XLOOKUP(B2,Table 1::A,Table 1::C,"not found",−1,1)

This will find the exact match or next smallest date in column A and return the value from column C.

Reply

Answer 2

Jul 23, 2025 01:56 PM in response to Badunit

Thanks, that helped!

Reply