Forum Discussion
How to check which date range an entered date falls between
Hi, I have the following table below. As shown below, I would like to user to enter the Part Number "P/N" (in B2) and Date Built (in B3) on Sheet 1. Based on this data, I want it to look up and ...
=LET(filldown,
SCAN("",A8:A21,LAMBDA(ini,arr,IF(arr<>"",arr,ini))),
revstartend,
FILTER(C8:E21,filldown=B2),
FILTER(CHOOSECOLS(revstartend,1),(CHOOSECOLS(revstartend,2)<=B3)*(CHOOSECOLS(revstartend,3)>=B3))
)
This is an alternative with Office 365 or Excel for the web.
OliverScheurich
Thanks so much for this fix. It works great!
Can you walk me through how this works?
Also, is there a way to modify this formula so that if the Date Built isn't within a date range, or no part number is entered, the Rev field is blank instead of the current #Calc?
Really appreciate the help!
An advantage of the formula that I used involving XLOOKUP is that most combinations of missing or invalid data return "Not available" rather than diverse errors. The formula itself is probably somewhat heavy going for anyone versed in more traditional methods though.
- Thanks Peter. And yes, this formula is heavy going as I am one versed in more traditional methods. : )
=LET(filldown,
SCAN("",A8:A21,LAMBDA(ini,arr,IF(arr<>"",arr,ini))),
revstartend,FILTER(C8:E21,filldown=B2),
IFERROR( FILTER(CHOOSECOLS(revstartend,1),(CHOOSECOLS(revstartend,2)<=B3)*(CHOOSECOLS(revstartend,3)>=B3)) ,""))
You are welcome. You can wrap the last calculation (FILTER) within the LET function into IFERROR (highlighted in red) in order to return a blank cell if the Date Built doesn't exist in the date range or if no part number is entered.
In the screenshot you can see the results of filldown and revstartend from the LET function for the PN from cell B2.
In the last step the function
IFERROR(FILTER(CHOOSECOLS(revstartend,1),(CHOOSECOLS(revstartend,2)<=B3)*(CHOOSECOLS(revstartend,3)>=B3)),"")
filters the data from range I3:K5 with the date from cell B3 and returns the Rev. or an empty cell.
Thanks again, that works great.
One last request. How can I make the ranges dynamic so that if they add more rows of data that the formula will still work?=LET(filldown,SCAN("",Tabelle9[PN],LAMBDA(ini,arr,IF(arr<>"",arr,ini))),
revstartend,FILTER(Tabelle9[[Revision]:[End]],filldown=B2),
IFERROR(FILTER(CHOOSECOLS(revstartend,1),(CHOOSECOLS(revstartend,2)<=B3)*(CHOOSECOLS(revstartend,3)>=B3)),""))You can work with a dynamic table. Tabelle9[PN] and Tabelle9[[Revision]:[End]] dynamically refer to the actual size of the dynamic table. Tabelle9 is the name of the dynamic table in my example. You have to replace Tabelle9 with the name of your dynamic table.
