I wonder what is the best formula in Numbers to Lookup Values (column C) if one date falls between two dates (columns A y B).
I haver checked all the numbers videos here. “Using Numbers Lookup With Date Ranges” (#1346) is something similar but it has only one column of sequenced dates.
It would be, maybe, interesting to have a video tutorial about this.
START END TYPE
01/01/1900 09/05/1985 A
10/05/1989 31/12/1994 B
01/01/1997 05/06/2013 C
06/06/2016 05/03/2019 D
06/03/2019 14/04/2023 E
Which type -if one- is 01/09/2016?
I think is a useful formula for many businesses to find a value corresponding a specific date.
I guess is not a simple lookup formula. Maybe a combination of INDEX-MATCH and others
—–
Enrique Moro
You could use MATCH to find the row with the start date that is the most one coming closest before the search date. Then another MATCH to find the row with the end date that is the closest after the search date. Compare those and if they are different then it means the date doesn't fall in a range of one of the rows. If they are the same though, then a LOOKUP on the start date will let you grab the value in C.
So something like this, where Table 1, column A is start date, column B is end date, column C is the type. Then what you see here as A2 is the first test value in a row in Table 2.
IF( MATCH(A2,Table 1::A,1) = MATCH(A2,Table 1::B,−1), LOOKUP(A2,Table 1::A,C), "Nope")
So pay careful attention to the third parameter in MATCH. The 1 looks for the value that is the last one that comes before it, and the -1 looks for the value that comes closest after it. Look at the Numbers Help for Match and read it carefully.
It works like a charm!
I tried some other options based in excel and none worked for me.
Thank you indeed! It will be very useful for one of my biggest spreadsheets