Formula for looking up matching values and approximate date?

N

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
225
Office Version
  1. 2016
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Try this in Tab1, cell D2 and drag down:
Excel Formula: 
=+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.
 
Upvote 0
excelhjalp said:
Try this in Tab1, cell D2 and drag down:
Excel Formula: 
=+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.
Click to expand...

Just keeps resulting in "NO" as the answer
 
Upvote 0
Noxqss38242 said:
Just keeps resulting in "NO" as the answer
Click to expand...
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.
 
Upvote 0
Tech Report Card Database.xlsx
BCDEF
1DATEUNIT
21/2/202217050NO
31/2/202220864NO
41/3/202220556NO
51/3/202220607NO
61/3/202220662NO
71/3/202220712NO
81/3/202220848NO
91/3/202220912NO
101/4/202217060NO
111/4/202220364NO
121/4/202220436NO
131/4/202220726NO
141/4/202220762
151/5/202217059
161/5/202220398
TAB1
Cell Formulas
RangeFormula
D2:D13D2=+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
AB
1UnitDate Received
2171324/18/2022
3171343/10/2022
4171332/1/2022
5171316/29/2022
6171305/10/2022
7171146/13/2022
8171142/1/2022
9170682/1/2022
10170288/4/2022
11170286/30/2022
12170281/25/2022
13170276/29/2022
14170272/10/2022
15170263/1/2022
16170256/24/2022
17170242/25/2022
18170223/7/2022
19170204/18/2022
20170198/3/2022
21170196/30/2022
22170186/10/2022
23170176/13/2022
24170167/19/2022
25170143/17/2022
26170135/31/2022
27170117/19/2022
28170076/24/2022
29170065/3/2022
30170056/10/2022
31170036/10/2022
32170093/24/2022
33208493/17/2022
34208481/20/2022
35208475/10/2022
36208466/28/2022
TAB2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I18,A:ACell ValueduplicatestextNO


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.
 
Upvote 0
Okay, I switched the ">=" mark in the formula. Please try this:

Excel Formula: 
=+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
ABCD
1date_serviceunitcheck
21.2.202217050NO
31.2.202220864NO
41.3.202220556NO
51.3.202220607NO
61.3.202220662NO
71.3.202220712NO
81.3.202220848YES
91.3.202220912NO
101.4.202217060NO
111.4.202220364NO
121.4.202220436NO
131.4.202220726NO
141.4.202220762NO
151.5.202217059NO
161.5.202220398NO
1724.1.202217114YES
1816.5.202217114YES
tab1
Cell Formulas
RangeFormula
D2:D18D2=+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
ABCD
1unitdate_oil
21713218.4.20220
3171343.10.20220
4171332.1.20220
51713129.6.20220
6171305.10.20220
71711413.6.20220
8171142.1.20220
9170682.1.20220
10170288.4.20220
111702830.6.20220
121702825.1.20220
131702729.6.20220
14170272.10.20220
15170263.1.20220
161702524.6.20220
171702425.2.20220
18170223.7.20220
191702018.4.20220
20170198.3.20220
211701930.6.20220
22170186.10.20220
231701713.6.20220
241701619.7.20220
251701417.3.20220
261701331.5.20220
271701119.7.20220
281700724.6.20220
29170065.3.20220
30170056.10.20220
31170036.10.20220
321700924.3.20220
332084917.3.20220
342084820.1.20221
35208475.10.20220
362084628.6.20220
37
tab2
Cell Formulas
RangeFormula
C2:C36C2=+COUNTIF('tab1'!$C$2:$C$16,'tab2'!A2)
 
Upvote 0
Solution
excelhjalp said:
Okay, I switched the ">=" mark in the formula. Please try this:

Excel Formula: 
=+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
ABCD
1date_serviceunitcheck
21.2.202217050NO
31.2.202220864NO
41.3.202220556NO
51.3.202220607NO
61.3.202220662NO
71.3.202220712NO
81.3.202220848YES
91.3.202220912NO
101.4.202217060NO
111.4.202220364NO
121.4.202220436NO
131.4.202220726NO
141.4.202220762NO
151.5.202217059NO
161.5.202220398NO
1724.1.202217114YES
1816.5.202217114YES
tab1
Cell Formulas
RangeFormula
D2:D18D2=+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
ABCD
1unitdate_oil
21713218.4.20220
3171343.10.20220
4171332.1.20220
51713129.6.20220
6171305.10.20220
71711413.6.20220
8171142.1.20220
9170682.1.20220
10170288.4.20220
111702830.6.20220
121702825.1.20220
131702729.6.20220
14170272.10.20220
15170263.1.20220
161702524.6.20220
171702425.2.20220
18170223.7.20220
191702018.4.20220
20170198.3.20220
211701930.6.20220
22170186.10.20220
231701713.6.20220
241701619.7.20220
251701417.3.20220
261701331.5.20220
271701119.7.20220
281700724.6.20220
29170065.3.20220
30170056.10.20220
31170036.10.20220
321700924.3.20220
332084917.3.20220
342084820.1.20221
35208475.10.20220
362084628.6.20220
37
tab2
Cell Formulas
RangeFormula
C2:C36C2=+COUNTIF('tab1'!$C$2:$C$16,'tab2'!A2)
Click to expand...


Works beautifully! Sorry took long to reply, had to go to court yesterday.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
577
JukeExcel
J
S
  • Locked
  • Question Question
Look up the values according to the conditions
Replies
1
Views
242
Peter_SSs
Peter_SSs
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
492
staticfluids
staticfluids
S
  • Question Question
Returning values with multiple conditions
Replies
0
Views
259
sslll
S
G
  • Solved
Parse dates into comma delimit values
Replies
3
Views
499
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top