Matching multiple ways?

[Noxqss38242]

I have many tabs with truck numbers to track a "reason", some tabs may contain the same truck but each tab is a different "reason"
I then have a tab of "open" work orders that list the "truck number" and the "reason" which can have multiple "reasons" and multiple truck numbers including duplicate truck numbers.

Campaign Report.xlsx
	D	E	F	G
359	17221		12/31/24 15:24	RECALL - 23509
360	17221		12/31/24 15:36	CAMPAIGN-22903
361	17249		11/10/22 15:20	BLIND SPOT RADAR
362	17299		07/11/23 09:45	CK.PASS SMART
363	17300		12/30/23 23:59	RECALL - 23509 MHM
364	17303		12/31/24 10:50	CAMPAIGN-23213 GHG LABEL
365	20659		03/11/23 23:58	RECALL - FL893A MHM
366	20669		07/27/23 15:08	ENGINE CODES PRESENT
367	20687		12/31/24 23:59	RECALL - FL893A
368	20827		12/31/24 10:55	CAMPAIGN - SF672-A
369	20833		07/12/23 13:01	REPAIR FLOOD DAMAGE
370	20843		11/23/22 13:08	NON OEM BATTERIES
371	20852		12/31/24 10:55	RECALL - FL851A
Sheet16

Cell Formulas
Range	Formula
F359	F359	="12/31/24 15:24"
F360	F360	="12/31/24 15:36"
F361	F361	="11/10/22 15:20"
F362	F362	="07/11/23 09:45"
F363	F363	="12/30/23 23:59"
F364	F364	="12/31/24 10:50"
F365	F365	="03/11/23 23:58"
F366	F366	="07/27/23 15:08"
F367	F367	="12/31/24 23:59"
F368,F371	F368	="12/31/24 10:55"
F369	F369	="07/12/23 13:01"
F370	F370	="11/23/22 13:08"

Example of "23509" tab

Campaign Report.xlsx
	A
1	17221
2	17222
3	17223
4	17224
5	17225
6	17226
7	17227
8	17228
9	17229
10	17230
11	17231
12	17232
13	17233
14	17234
23509

I'm guessing each tab will have their own formula which is fine, I can adjust for tab names. Just need a formula for the tab to tell me if "Sheet16" contains the unit number and "reason" I'm looking for.

So if sheet16 says 17221 has reason, "*23509" mentioned, then I need the 23509 tab for truck 17221 to say "Open", if there is no 17221 with a reason "*23509" mentioned, I need it to say "Closed" in the cell next to it. Formula would be in row B.
There might be 10 instances of 17221 but need it to search all instances for "*23509" in the reason line which is column "G".

If I need a helper cell to do so, I have no problem inserting it into the 23509 tab, etc...
The only tab that will constantly change is "Sheet16" which will update each week when I drop in new data.

 

[Noxqss38242]

=IF(COUNTIF(Sheet16!$D:$D,'23509'!A1),AND(IF(COUNTIF(Sheet16!$G:$G,"*23509*"),"Open","Closed")))

That formula doesn't work, still trying to figure it out.
If cell a1 appears in range AND if the corresponding column "G" contains "*23509*" then return "Open" if not then return "Closed".
(at least that is what I thought I was telling it to do....haha)

Any help would be appreciated...

 

[Noxqss38242]

=IF(AND(COUNTIF(Sheet16!$D:$D,'23509'!A1),COUNTIF(Sheet16!G:G,"*23509*")),"Open","Closed")

This formula returns data ("Open" "Closed") other than "#value" but the data doesn't match....getting closer but still need help....

 

[Noxqss38242]

Okay, finally got it....trial and error....took too long though for me to figure it out....but got it down....

=IF(COUNTIFS(Sheet16!$D:$D,'216A2'!A1,Sheet16!$G:$G,"*216A2*"),"Open","Closed")