Index and match from different sheet with if statement?

[Charlie109]

I need to return value from another worksheet.

Sheet1 has the raw data to pull from
Col A= Emp ID
Col G = PEL ID
Col L = Expiry Date
Col M = Credit Result

I need to look up an EmpID from another sheet (called 33685 DE Qual Status).
Row 2 = all individual EmpID
Col A = the PEL ID
Column below each EmpID required the credit result from sheet1 (Credit result should be either a P, a date or REQ'D
if ColL in sheet1 has a date, that is the value to enter in sheet 33685 DE Qual Status
if there is no date in ColL then enter value that is in ColM (should be P); if the value is E, change it to P and if there is no match the value should be REQ'D

Attached is a spreadsheet.

Appreciate your help

 

[Charlie109]

Attach

DE Qual tracking-Updated Jan 12 2022.xlsx
	A	B	C	D	E	F	G
1	PEL	Title	Time	Type	Name ->
2					EE ID ->	180650	801801
3					QUAL 33685	IN PROGRESS	APPROVED
4	PEL ID	PEL Name	DURATION	ENVIRONMENT	TYPE
5	369	INTRO TO CODES & JURISDICTIONAL REQUIREMENTS FOR PRESSURE BOUNDARIES - CAL	4	CBT	CRS	REQ'D	#N/A
6	3520	INTRODUCTION TO CANDU - CBT	8	CBT	CRS	REQ'D	Credit Result
7	3659	BASIC NUCLEAR THEORY - CBT	7	CBT	CRS	REQ'D	Credit Result
8	3875	INTRODUCTION TO EQ ENGINEERING - CBT	3	CBT	CRS	P	P
9	13128	PRINCIPLES OF NUCLEAR SAFETY	8	CLASSROOM	CRS	REQ'D	P
10	13505	PROFESSIONAL ENGINEERS ACT & DESIGN AUTHORITY - CAL	2	CAL	ARQ	REQ'D	P
11	21803	OPERATING POLICIES & PRINCIPLES INTRODUCTION FOR ENGINEERING STAFF - CAL	2	CBT	CRS	REQ'D	P
12	25663	INTRODUCTION TO OPERATING EXPERIENCE - CBT	2	CBT	CRS	REQ'D	P
13	28624	PRESSURE BOUNDARY PROGRAM OVERVIEW - CAL	2	CBT	CRS	11-Aug-24	REQ'D
14	28738	DE EVALUATION - DESIGN REQUIREMENTS - PEV	2	OJT	JPM	REQ'D	P
15	28739	DE EVALUATION - CALCULATIONS & ANALYSIS - PEV	3	OJT	JPM	REQ'D	P
16	28740	DE EVALUATION - ENGINEERING SPECIFICATIONS - PEV	3	OJT	JPM	REQ'D	P
17	28743	DE EVALUATION - PLANT MODIFICATIONS - PEV	2	OJT	JPM	REQ'D	P
18	64196	SEISMIC QUALIFICATION FOR ENGINEERS - CAL	2	CAL	ARQ	REQ'D	P
19	65863	TECHNICAL OPERABILITY EVALUATION (TOE) - CAL	1	CBT	CRS	REQ'D	P
20	66157	RISK BASED MODIFICATION PROCESS OVERVIEW - CAL	3	CBT	CRS	REQ'D	P
21	66561	RECORDS MANAGEMENT AWARENESS - CAL	2	CAL	ARQ	REQ'D	P
22	66978	DISCOVERY ISSUE RESOLUTION PROCESS (DIRP) - CAL	72	CBT	CRS	REQ'D	P
23	67329	PROCEDURAL COMPLIANCE - CAL	2	CBT	CRS	REQ'D	P
24	67882	ENGINEERING BEHAVIOURS - CAL	1	CAL	ARQ	REQ'D	REQ'D
25	68601	AGING MANAGEMENT - CAL	4	CBT	CRS	REQ'D	P
26	69122	ADVANCED PRINCIPLES OF NUCLEAR SAFETY	3	CLASSROOM	CRS	REQ'D	P
27	69151	SCIENTIFIC, ENGINEERING & SAFETY ANALYSIS (SESA) SOFTWARE - CAL	8	CBT	CRS	REQ'D	P
28	69451	INTRODUCTION TO RISK MANAGEMENT AND MARGIN MANAGEMENT - CAL	2	CBT	CRS	REQ'D	P
29	70843	CSA N285 OVERVIEW - CAL	2	CBT	CRS	REQ'D	P
30	70844	CSA N286 OVERVIEW - CAL		CBT	CRS	REQ'D	P
31	70845	INTRODUCTION TO DESIGN BASIS		CLASSROOM	CRS	REQ'D	P
32	77796	INTRODUCTION TO CSA N299 - CAL		CAL	ARQ	REQ'D	P
33	78129	NUCLEAR NON-PROLIFERATION IMPORT AND EXPORT CONTROL REGULATIONS		CAL	ARQ	REQ'D	P
33685 DE Qual Status

Cell Formulas
Range	Formula
G5	G5	=INDEX(Sheet1!M:M,MATCH('33685 DE Qual Status'!$G$2,A5,0))
G6:G7	G6	=INDEX(Sheet1!M:M,MATCH('33685 DE Qual Status'!$G$2,A6))

ed is the file

 

[Charlie109]

Sheet1

DE Qual tracking-Updated Jan 12 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Emp ID	Last Name	First Name	MJF	Type	Supv ID	PEL ID	Location	PEL Name	Active	Credit Date	Expiry Date	Credit Result
2	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	67882	xxxN	ENxINEERINx BEHAVIxURS - CAL	Y	31-May-21	30-Jun-22	P
3	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	64246	xxxN	NxET CxNTINUINx TRAININx CBT	Y	28-May-21	30-Jun-23	P
4	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	70577	xxxN	NxET CxNTINUINx TRAININx FxR NUCLEAR SUxxxRT STAFF - CBT	Y	28-May-21	30-Jun-23	P
5	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	76782	Rx	WHMIS FxR Rx ANNUAL CxNTINUINx TRAININx - CBT	Y	28-May-21	30-Jun-22	P
6	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	77107	DND	REFURBISHMENT INTERFACE AND ISLANDINx TRAININx TIER 2 - CxNTRACTxR STAFF - CAL	Y	24-Mar-21		P
7	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	23783	xxx	RESxIRATxR MEDICAL ASSESSMENT - xxx	Y	08-Mar-21	08-Dec-22	P
8	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	369	xxxN	CxDE-xVER-CxDE REVIEW - ASME SECTIxN III CxDE CHANxES REFRESHER - CAL	Y	24-Feb-20	24-Feb-23	P
9	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	66979	xxxN	TxE AND DIRx REFRESHER TRAININx AND xxEX - CAL	Y	14-Jan-20	31-Mar-23	P
10	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	70964	xxxN	TxE AND DIRx REFRESHER - CAL - TRACKINx xEL (CxMxLETE xEL 66979)	Y	14-Jan-20	31-Mar-23	P
11	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	65799	xxxN	ENVIRxNMENTAL QUALIFICATIxN REFRESHER FxR ENxINEERINx - CAL	Y	10-Jan-20	31-Mar-23	P
12	801801	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	61146	xxxN	SAFE xxERATINx ENVELxxE - CAL	Y	02-Dec-19	02-Dec-22	P
Sheet1