SOLVED

look for a specific date from table of ranges of dates

Copper Contributor

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

101/01/202315/01/202340
216/01/202331/01/202340
301/02/202314/02/202338
415/02/202328/02/202333
501/03/202315/03/202332
616/03/202331/03/202332
701/04/202315/04/202328
816/04/202330/04/202326
901/05/202315/05/202324
1016/05/202331/05/202321
4 Replies

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

 

best response confirmed by Hans Vogelaar (MVP)
Solution

Hi @Asaf_AM 

Alternatively with Excel 2021/365

Sample.png

in G2

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

@L z. Worked perfect! thanks to you all

@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

1 best response

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

Hi @Asaf_AM 

Alternatively with Excel 2021/365

Sample.png

in G2

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

View solution in original post