Require Formula Help

[Ron99]

Hello,

I have 2 set of data, one is the source data and the other is output.

Below is the source data

pn	dt1	dt2
110​	11-Jan-19​	20 May 2019​
109​	10-Jan-19​	19 May 2019​
101​	9-Jan-19​	18 May 2019​
107​	8-Jan-19​	17 May 2019​
106​	7-Jan-19​	16 May 2019​
101​	6-Jan-19​	15 May 2019​
104​	5-Jan-19​	14 May 2019​
103​	4-Jan-19​	13 May 2019​
102​	3-Jan-19​	12 May 2019​
101​	02 January 2019​	11 May 2019​

1ST COLUMN are the part numbers, 2nd and 3rd column are dates

OUTPUT DATA

pn	dt1	Output
101​	12 May 2019​	TRUE​
102​	03 January 2019​
103​	04 January 2019​
104​	05 January 2019​
105​	06 January 2019​
106​	07 January 2019​
107​	08 January 2019​

The formula that I require is there are part numbers in the output data which needs to lookup from the source data and check by row if the date falls within the range if yes, then the formula should return value as true.

For example - PN 101, Date - 12 May 2019,
Source data has PN 101 in row 3, 6 & 10. The formula should lookup each row and check if the date range falls within the given date, if yes then "true" if not "false".

 

[mikerickson]

Try putting this in C3 of Output sheet.

=AND(VLOOKUP(A2, Source!$A$1:$C10), 2, False) <= B2, B2<=VLOOKUP(A2, Source!$A$1:$C10), 3, False) )

 

[Ron99]

Superb! thank you sir.

 

[Ron99]

I noticed that the VLOOKUP function just looks up the first data source, I have data where the 1st row data's date is year 2020 but the second one falls within the range, whereas the current function is unable to identify that.

 

[mikerickson]

something like =COUNTIFS(Source!$A:$A, A2, Source!$B:$B, "<"&B2, Source$C:$C, ">"&B2)

 

[Ron99]

Thank you sir. I appreciate it, This function now captures any date range.