Match multiple criteria with date range

[Newbie987654321]

Hi Guys. I so badly need your help.
I want to know the STATUS of Table 1.

CRITERIA OF SUBMITTED STATUS:
-DATE (Table 2) > or equal to Actual Date (Table 1)
-Name, Supervisor and Infraction should match.

If no match, then it should be "PENDING".

I need a formula that would show if it has been "Submitted" or still "Pending" under the STATUS Column.

Here's an example table which shows the STATUS.

[TABLE="class: cms_table, width: 368"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STATUS[/TD]
[TD]Name[/TD]
[TD]Supervisor[/TD]
[TD]Infraction[/TD]
[TD]Actual Date[/TD]
[/TR]
[TR]
[TD]Submitted[/TD]
[TD]John Smith[/TD]
[TD]April Allens[/TD]
[TD]Tardiness[/TD]
[TD]18-Mar-18[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]John Smith[/TD]
[TD]April Allens[/TD]
[TD]Tardiness[/TD]
[TD]23-Mar-18[/TD]
[/TR]
[TR]
[TD]Submitted[/TD]
[TD]Carl Lee[/TD]
[TD]April Allens[/TD]
[TD]Tardiness[/TD]
[TD]18-Mar-18[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]Carl Lee[/TD]
[TD]April Allens[/TD]
[TD]Tardiness[/TD]
[TD]23-Mar-18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Supervisor[/TD]
[TD]Frequency[/TD]
[TD]Date Submitted[/TD]
[TD]Infraction[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]April Allens[/TD]
[TD]1st instance[/TD]
[TD]21-Mar-18[/TD]
[TD]Tardiness[/TD]
[/TR]
[TR]
[TD]Carl Lee[/TD]
[TD]Sue Pervisor[/TD]
[TD]1st instance[/TD]
[TD]8-Mar-18[/TD]
[TD]Tardiness[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]April Allens[/TD]
[TD]1st instance[/TD]
[TD]21-Mar-18[/TD]
[TD]Absence[/TD]
[/TR]
[TR]
[TD]Carl Lee[/TD]
[TD]Sue Pervisor[/TD]
[TD]1st instance[/TD]
[TD]8-Mar-18[/TD]
[TD]Absence[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I don't see why the 3rd one is "Submitted" as Name and Supervisor do not match in any rows. Assuming that is a typo, try this copied down.

Excel Workbook
	A	B	C	D	E
1	Table 1
2	STATUS	Name	Supervisor	Infraction	Actual Date
3	Submitted	John Smith	April Allens	Tardiness	18-Mar-18
4	Pending	John Smith	April Allens	Tardiness	23-Mar-18
5	Pending	Carl Lee	April Allens	Tardiness	18-Mar-18
6	Pending	Carl Lee	April Allens	Tardiness	23-Mar-18
7
8	Table 2
9	Name	Supervisor	Frequency	Date Submitted	Infraction
10	John Smith	April Allens	1st instance	21-Mar-18	Tardiness
11	Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Tardiness
12	John Smith	April Allens	1st instance	21-Mar-18	Absence
13	Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Absence
Status

If those tables are 'formal' tables, this would be the alternative that would automatically expand if the tables are added to.

Excel Workbook
	A	B	C	D	E
1	Table 1
2	STATUS	Name	Supervisor	Infraction	Actual Date
3	),"Submitted","Pending")]Submitted	John Smith	April Allens	Tardiness	18-Mar-18
4	Pending	John Smith	April Allens	Tardiness	23-Mar-18
5	Pending	Carl Lee	April Allens	Tardiness	18-Mar-18
6	Pending	Carl Lee	April Allens	Tardiness	23-Mar-18
7
8	Table 2
9	Name	Supervisor	Frequency	Date Submitted	Infraction
10	John Smith	April Allens	1st instance	21-Mar-18	Tardiness
11	Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Tardiness
12	John Smith	April Allens	1st instance	21-Mar-18	Absence
13	Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Absence
Status (Tables)

 

[Newbie987654321]

THIS IS AMAZING!!! I got it on the first table. It almost took me a month, I still couldn't figure it out. HAHAHA.. Thank you soooooo much!

PS. And yes, it was a typo on the table. Thank you soooo much for the help Peter. You're the man!

 

[Peter_SSs]

You are welcome. Glad it worked out so well for you.