IF(IFERROR(MATCH

[luisitocarrion1900]

I have the below formula but am trying to add that if AI Status'!E:E is blank or contain semi column : enter the words "AI Pending Submission"

=IF(IFERROR(MATCH([@[Business Unit]],'AI Status'!B:B,0),0)=0,"Pending AI Submission",IF(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)="A/LM/PMP/PM Approved","PM Approved",IF(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)="Extension","AI Extension",IF(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)="PMO Approved","Waiting PM Approval",IF(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)="Waiver","PM Waiver",IF(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)="PM Certified","PM Certified",IF(AND(DATEVALUE([@[Extension Date]])<TODAY(),OR(INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)<>"A/LM/PMP/PM Approved", INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)<>"PM Approved")),"Delinquent","No AI Status")))))))

 

[StephenCrump]

I'd make life easier by simplifying as follows:

	A	B	C	D	E
1
2		Blah	Business unit	Extension Date	MyFormula
3		1	ABC		PM waiver
4		2	XYZ		PM approved
5
6
7			MyTable
8			A/LM/PMP/PM Approved	PM approved
9			Extension	AI Extension
10			PMO approved	Waiting PM approval
11			Waiver	PM waiver
12			PM Certified	PM Certified
Sheet1

Cell Formulas
Range	Formula
E3:E4	E3	=LET(x,INDEX('AI Status'!E:E,MATCH([@[Business unit]],'AI Status'!B:B,)),VLOOKUP(x,MyTable,2,))

Named Ranges
Name	Refers To	Cells
MyTable	=Sheet1!$C$8:$D$12	E3:E4

	A	B	C	D	E
1
2
3
4
5		ABC			Waiver
6
7
8		XYZ			A/LM/PMP/PM Approved
9
AI Status

I'm not sure about the date part of your formula. You don't need this test: INDEX('AI Status'!E:E, MATCH([@[Business Unit]],'AI Status'!B:B,0),1)<>"A/LM/PMP/PM Approved" because it will necessarily be true (your formula has already tested for equality). Is "PM approved" a value you need to test for?

And when you say: AI Status'!E:E ... contain semi column, do you mean a single character ";" or a semi-colon anywhere in the cell?