How to check which date range an entered date falls between

[spinaz007]

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 populate the correct revision (Rev) number in B4.  Currently, I am using the following formula (in B4) which returns Rev 10 as shown.  =IF(AND(B3>=D8,B3<=E8),C8,IF(AND(B3>=D9,B3<=E9),C9,IF(AND(B3>=D10,B3<=E10),C10,IF(AND(B3>=D11,B3<=E11),C11,""))))
This formula is currently not dependent on the P/N and just checks the date range for P/N 12345678, as called out in the formula.  Since there is overlap between the date ranges for the different part numbers, I need for it to only check the dates listed for the P/N entered, and then return the Revision according to which date range for that part number the entered Date Built matches. 
For this example, Since P/N 12345678 was entered, it would only check the date ranges D8:E11.  Then, it would need to compare the Date Built that was entered (12/28/2023 in B3) with the dates in that range (D8:E11) to see which it fell between.  In this case, that would be the range D8:E8.  Therefore, it should populate Rev 10.  If however the P/N entered was 12345679, then it should populate Rev 6.
Thanks for any help on this!

[#2]

@spinaz007 

=INDEX($C$8:$C$21,MATCH(1,($D$8:$D$21<=$B$3)*(E8:$E$21>=B3)*($F$8:$F$21=$B$2),0))

You can apply this formula along with a helper column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

=IF(A8<>"",A8,F7)

This is the formula in cell F8 for the helper column.

If you work with Office 365 or Excel for the web you can apply SCAN and LAMBDA within one formula and a helper column isn't required.

[#3]

@spinaz007 

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

[#4]

@spinaz007 

As suggested by @OliverScheurich but written in a way that merges with my general spreadsheet presentation style

= LET(
    inDate?,     BYROW(dateRange<built, XORλ),
    filled,      SCAN("", partNo, FILLDOWNλ),
    validParts,  IF(inDate?, filled),
    XLOOKUP(part, validParts, revision, "Not available")
  )

where

FILLDOWNλ(p, q)
= IF(q <> "", q, p)

XORλ(v)
= XOR(v)

[#5]

Version from part and date

[#6]

@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!

[#7]

@spinaz0070 

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

[#8]

@OliverScheurich 

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?

[#9]

@spinaz007 

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

[#10]

@spinaz0070 

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.

[#11]

Thanks!

[#12]

Thanks Peter. And yes, this formula is heavy going as I am one versed in more traditional methods. : )