Index, match and return value (with if statements)?

[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

Appreciate your help

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))

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	98765	xLHxxx	xLIx	ENG	Externxl Servixes - xxxI - KINExTRIxSINx	204252 - SxxxOxIN	369	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	98765	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		E
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
Sheet1

 

[GlennUK]

I'm sorry, but this is very difficult to understand - mainly because you have nonsense INDEX formulas in there to confuse matters. Also it seems that the requirement is not to fetch on Emp ID, but on matching both an Emp ID and Pel ID - is that the case?