Formula for looking up matching values and approximate date?

[Noxqss38242]

TAB1 "Oil Sample" is master sheet
TAB2 "Data" is the data

Tab 1
column C are unit numbers, ie: 20848, 20364, 17060, 16985, etc...
column B is when the service was done, ie: 1/4/2022, 1/10/2022, 4/3/2022, etc...

Tab 2
column A are the unit numbers, ie: 20848, 20364, 17060, etc...
column B are the dates oil sample was logged, ie: 1/8/2022, 1/15/2022, 2/5/2022, etc...

The units numbers will match, but the dates will NOT, so I was wanting to do a formula that looked for a few things.
1) If the unit in tab1 column c is found on tab2 column a, then...
2) If tab1 column B minus tab2 column B is less than 50, then return "YES", if not, then return "NO".
*50 is just an example of days lapsed, as I don't want too many days between because the unit may show up later in the year for another test.

3) So it must also distinguish the fact that say, 17028 had a sample done in January and again in June and will have multiple date sets.

Possible to do this or is this one too complicated and I should limit my date ranges for it to only look once and not for possible multiple instances?

 

[excelhjalp]

Try this in Tab1, cell D2 and drag down:

=+IF(COUNTIFS('Tab2'!$A$2:$A$19,'Tab1'!C2,'Tab2'!$B$2:$B$19,"<="&'Tab1'!B2-50,'Tab2'!$B$2:$B$19,">="&'Tab1'!B2-150)>0,"YES","NO")

In this it resets according to your rule number 3 after 150 days.

 

[Noxqss38242]

Try this in Tab1, cell D2 and drag down:

=+IF(COUNTIFS('Tab2'!$A$2:$A$19,'Tab1'!C2,'Tab2'!$B$2:$B$19,"<="&'Tab1'!B2-50,'Tab2'!$B$2:$B$19,">="&'Tab1'!B2-150)>0,"YES","NO")

In this it resets according to your rule number 3 after 150 days.

Just keeps resulting in "NO" as the answer

 

[excelhjalp]

Just keeps resulting in "NO" as the answer

Could you provide dummy data with xl2bb with your expected outcome? It should just be a matter of reversing the "<=" and ">=" commands. The code workes in regards to how I understand the question.

 

[Noxqss38242]

Tech Report Card Database.xlsx
	B	C	D	E	F
1	DATE	UNIT
2	1/2/2022	17050	NO
3	1/2/2022	20864	NO
4	1/3/2022	20556	NO
5	1/3/2022	20607	NO
6	1/3/2022	20662	NO
7	1/3/2022	20712	NO
8	1/3/2022	20848	NO
9	1/3/2022	20912	NO
10	1/4/2022	17060	NO
11	1/4/2022	20364	NO
12	1/4/2022	20436	NO
13	1/4/2022	20726	NO
14	1/4/2022	20762
15	1/5/2022	17059
16	1/5/2022	20398
TAB1

Cell Formulas
Range	Formula
D2:D13	D2	=+IF(COUNTIFS('TAB2'!$A$2:$A$19,'TAB1'!C2,'TAB2'!$B$2:$B$19,"<="&'TAB1'!B2-50,'TAB2'!$B$2:$B$19,">="&'TAB1'!B2-150)>0,"YES","NO")

Tech Report Card Database.xlsx
	A	B
1	Unit	Date Received
2	17132	4/18/2022
3	17134	3/10/2022
4	17133	2/1/2022
5	17131	6/29/2022
6	17130	5/10/2022
7	17114	6/13/2022
8	17114	2/1/2022
9	17068	2/1/2022
10	17028	8/4/2022
11	17028	6/30/2022
12	17028	1/25/2022
13	17027	6/29/2022
14	17027	2/10/2022
15	17026	3/1/2022
16	17025	6/24/2022
17	17024	2/25/2022
18	17022	3/7/2022
19	17020	4/18/2022
20	17019	8/3/2022
21	17019	6/30/2022
22	17018	6/10/2022
23	17017	6/13/2022
24	17016	7/19/2022
25	17014	3/17/2022
26	17013	5/31/2022
27	17011	7/19/2022
28	17007	6/24/2022
29	17006	5/3/2022
30	17005	6/10/2022
31	17003	6/10/2022
32	17009	3/24/2022
33	20849	3/17/2022
34	20848	1/20/2022
35	20847	5/10/2022
36	20846	6/28/2022
TAB2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:I18,A:A	Cell Value	duplicates	text	NO

With the sample range that is here, you see that 20848 is the only one but the list is over 700 rows. Nonetheless, 20848 service was done on 1/3/22 and the sample was received on 1/20/22 which is within the 50 day window, so should return "YES". What is also available to view is that 17114 has 2 received samples one on 6/13/22 and the other on 2/1/22 which eventually is on the service tab (TAB1) done on 1/24/22 and 5/16/22. Both should also return yes.

 

[excelhjalp]

Okay, I switched the ">=" mark in the formula. Please try this:

=+IF(COUNTIFS('tab2'!$A$2:$A$36,'tab1'!C2,'tab2'!$B$2:$B$36,">="&'tab1'!B2-50,'tab2'!$B$2:$B$36,">="&'tab1'!B2-150)>0,"YES","NO")

Here's the result:

Tab1:

qqqqq.xlsx
	A	B	C	D
1		date_service	unit	check
2		1.2.2022	17050	NO
3		1.2.2022	20864	NO
4		1.3.2022	20556	NO
5		1.3.2022	20607	NO
6		1.3.2022	20662	NO
7		1.3.2022	20712	NO
8		1.3.2022	20848	YES
9		1.3.2022	20912	NO
10		1.4.2022	17060	NO
11		1.4.2022	20364	NO
12		1.4.2022	20436	NO
13		1.4.2022	20726	NO
14		1.4.2022	20762	NO
15		1.5.2022	17059	NO
16		1.5.2022	20398	NO
17		24.1.2022	17114	YES
18		16.5.2022	17114	YES
tab1

Cell Formulas
Range	Formula
D2:D18	D2	=+IF(COUNTIFS('tab2'!$A$2:$A$36,'tab1'!C2,'tab2'!$B$2:$B$36,">="&'tab1'!B2-50,'tab2'!$B$2:$B$36,">="&'tab1'!B2-150)>0,"YES","NO")

Tab2:

qqqqq.xlsx
	A	B	C	D
1	unit	date_oil
2	17132	18.4.2022	0
3	17134	3.10.2022	0
4	17133	2.1.2022	0
5	17131	29.6.2022	0
6	17130	5.10.2022	0
7	17114	13.6.2022	0
8	17114	2.1.2022	0
9	17068	2.1.2022	0
10	17028	8.4.2022	0
11	17028	30.6.2022	0
12	17028	25.1.2022	0
13	17027	29.6.2022	0
14	17027	2.10.2022	0
15	17026	3.1.2022	0
16	17025	24.6.2022	0
17	17024	25.2.2022	0
18	17022	3.7.2022	0
19	17020	18.4.2022	0
20	17019	8.3.2022	0
21	17019	30.6.2022	0
22	17018	6.10.2022	0
23	17017	13.6.2022	0
24	17016	19.7.2022	0
25	17014	17.3.2022	0
26	17013	31.5.2022	0
27	17011	19.7.2022	0
28	17007	24.6.2022	0
29	17006	5.3.2022	0
30	17005	6.10.2022	0
31	17003	6.10.2022	0
32	17009	24.3.2022	0
33	20849	17.3.2022	0
34	20848	20.1.2022	1
35	20847	5.10.2022	0
36	20846	28.6.2022	0
37
tab2

Cell Formulas
Range	Formula
C2:C36	C2	=+COUNTIF('tab1'!$C$2:$C$16,'tab2'!A2)

 

[Noxqss38242]

Okay, I switched the ">=" mark in the formula. Please try this:

=+IF(COUNTIFS('tab2'!$A$2:$A$36,'tab1'!C2,'tab2'!$B$2:$B$36,">="&'tab1'!B2-50,'tab2'!$B$2:$B$36,">="&'tab1'!B2-150)>0,"YES","NO")

Here's the result:

Tab1:

qqqqq.xlsx
	A	B	C	D
1		date_service	unit	check
2		1.2.2022	17050	NO
3		1.2.2022	20864	NO
4		1.3.2022	20556	NO
5		1.3.2022	20607	NO
6		1.3.2022	20662	NO
7		1.3.2022	20712	NO
8		1.3.2022	20848	YES
9		1.3.2022	20912	NO
10		1.4.2022	17060	NO
11		1.4.2022	20364	NO
12		1.4.2022	20436	NO
13		1.4.2022	20726	NO
14		1.4.2022	20762	NO
15		1.5.2022	17059	NO
16		1.5.2022	20398	NO
17		24.1.2022	17114	YES
18		16.5.2022	17114	YES
tab1

Cell Formulas
Range	Formula
D2:D18	D2	=+IF(COUNTIFS('tab2'!$A$2:$A$36,'tab1'!C2,'tab2'!$B$2:$B$36,">="&'tab1'!B2-50,'tab2'!$B$2:$B$36,">="&'tab1'!B2-150)>0,"YES","NO")

Tab2:

qqqqq.xlsx
	A	B	C	D
1	unit	date_oil
2	17132	18.4.2022	0
3	17134	3.10.2022	0
4	17133	2.1.2022	0
5	17131	29.6.2022	0
6	17130	5.10.2022	0
7	17114	13.6.2022	0
8	17114	2.1.2022	0
9	17068	2.1.2022	0
10	17028	8.4.2022	0
11	17028	30.6.2022	0
12	17028	25.1.2022	0
13	17027	29.6.2022	0
14	17027	2.10.2022	0
15	17026	3.1.2022	0
16	17025	24.6.2022	0
17	17024	25.2.2022	0
18	17022	3.7.2022	0
19	17020	18.4.2022	0
20	17019	8.3.2022	0
21	17019	30.6.2022	0
22	17018	6.10.2022	0
23	17017	13.6.2022	0
24	17016	19.7.2022	0
25	17014	17.3.2022	0
26	17013	31.5.2022	0
27	17011	19.7.2022	0
28	17007	24.6.2022	0
29	17006	5.3.2022	0
30	17005	6.10.2022	0
31	17003	6.10.2022	0
32	17009	24.3.2022	0
33	20849	17.3.2022	0
34	20848	20.1.2022	1
35	20847	5.10.2022	0
36	20846	28.6.2022	0
37
tab2

Cell Formulas
Range	Formula
C2:C36	C2	=+COUNTIF('tab1'!$C$2:$C$16,'tab2'!A2)

Works beautifully! Sorry took long to reply, had to go to court yesterday.

 

[excelhjalp]

Great to hear. Take care.

Regards,
Sveppi