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 1
STATUS	Name	Supervisor	Infraction	Actual Date
Submitted	John Smith	April Allens	Tardiness	18-Mar-18
Pending	John Smith	April Allens	Tardiness	23-Mar-18
Submitted	Carl Lee	April Allens	Tardiness	18-Mar-18
Pending	Carl Lee	April Allens	Tardiness	23-Mar-18
Table 2
Name	Supervisor	Frequency	Date Submitted	Infraction
John Smith	April Allens	1st instance	21-Mar-18	Tardiness
Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Tardiness
John Smith	April Allens	1st instance	21-Mar-18	Absence
Carl Lee	Sue Pervisor	1st instance	8-Mar-18	Absence

<tbody>
</tbody>

 

[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.