look for a specific date from table of ranges of dates

[Asaf_AM]

Hello everyone and thanks from advanced.

I need to look for a specific date (receiving date) from other table with ranges of dates (each month divided for 2) and take the value of the cell output cell of this period (lead time).

receiving date        lead time

30/03/2023
21/03/2023

period     from                    to               lead time

1	01/01/2023	15/01/2023	40
2	16/01/2023	31/01/2023	40
3	01/02/2023	14/02/2023	38
4	15/02/2023	28/02/2023	33
5	01/03/2023	15/03/2023	32
6	16/03/2023	31/03/2023	32
7	01/04/2023	15/04/2023	28
8	16/04/2023	30/04/2023	26
9	01/05/2023	15/05/2023	24
10	16/05/2023	31/05/2023	21

[#2]

@Asaf_AM 

Let's say that the table with the ranges is on a sheet named Data in A2:D11 (with headers in row 1).

And the receiving dates are in A2 and down on another sheet.

Enter the following formula in B2, then fill down:

=IFERROR(VLOOKUP(A2,Data!$B$2:$D$11,3),"")

[#3 · Accepted solution]

Hi @Asaf_AM 

Alternatively with Excel 2021/365

in G2

=XLOOKUP(F3, Table1[From], Table1[Lead Time], "no match", -1)

[#4]

@L z. Worked perfect! thanks to you all

[#5]

@Asaf_AM 

Glad we could help & you have a solution
Next time(s) don't miss the 'Mark as solution' link at the bottom of the replies you get - @Hans Vogelaar was kind enough to do it on your behalf but has more important things to do as a key contributor to this forum - Thanks & nice EOD