SOLVED

# look for a specific date from table of ranges of dates

Copper Contributor

# look for a specific date from table of ranges of dates

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).

 30/03/2023 21/03/2023

 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
4 Replies

# Re: look for a specific date from table of ranges of dates

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),"")

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: look for a specific date from table of ranges of dates

Hi @Asaf_AM

Alternatively with Excel 2021/365

in G2

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

# Re: look for a specific date from table of ranges of dates

@L z. Worked perfect! thanks to you all

# Re: look for a specific date from table of ranges of dates

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

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: look for a specific date from table of ranges of dates

Hi @Asaf_AM

Alternatively with Excel 2021/365

in G2

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