IF for Data that matches in a Different Sheet

[luisitocarrion1900]

Hello,

I have a sheet name "AIM" and a sheet name "AI Status"

I need to place a formula on the sheet named "AIM" column named "Post Status", that if Column named "Business Unit" data matches the data on the sheet Named "AI Status" Column named "Business Unit" and the data on the sheet named "AIM" column named "Submission Status" says "A/LM/PMP/PM Approved" and enter the words "Completed" in the AIM sheet column named "Post Status" but if the date in the sheet named "AIM" column named "Extension Date" is passed today's date and the words A/LM/PMP/PM Approved or PMO Approved are not in sheet AI Status column Named "Submission Status" enter the words "Delinquent" in sheet named "AIM" Column named "BU Status" but if the date has not been met and the the status of A/LM/PMP/PM Approved or PMO Approved is not there enter Pending

See both sheets below the first sheet is the AIM Sheet and the second will be the AI Status Sheet.

AIM Sheet

AI Status Sheet

 

[cyrilbrd]

Hi,
Maybe something along:

Mrexcel.xlsx
	A	B	C	D	E
1	AIM
2	BU	BUD	Post	Pstatus	extension date
3	yaoun	here	this	completed	02/27/2020
4	nssit	there	that	delinquent	02/28/2020
5	cooit	anywhere	these	delinquent	02/29/2020
6	txlit			completed	02/29/2020
7
8	AI
9	BU	D	SC	Status
10	yaoun	this		A/LP/PMP/PM Approved
11	txlit	that		PMO Approved
12	cooit	those		A/LP Approved
luisitocarrion1900

Cell Formulas
Range	Formula
D3:D6	D3	=IF(AND([@[extension date]]>TODAY()=TRUE,OR(XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="A/LP/PMP/PM Approved",XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="PMO Approved")=TRUE),"completed", IF(AND([@[extension date]]>TODAY()=FALSE,OR(XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="A/LP/PMP/PM Approved",XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="PMO Approved")=TRUE),"completed","delinquent"))

 

[luisitocarrion1900]

Hi,
Maybe something along:

Mrexcel.xlsx
	A	B	C	D	E
1	AIM
2	BU	BUD	Post	Pstatus	extension date
3	yaoun	here	this	completed	02/27/2020
4	nssit	there	that	delinquent	02/28/2020
5	cooit	anywhere	these	delinquent	02/29/2020
6	txlit			completed	02/29/2020
7
8	AI
9	BU	D	SC	Status
10	yaoun	this		A/LP/PMP/PM Approved
11	txlit	that		PMO Approved
12	cooit	those		A/LP Approved
luisitocarrion1900

Cell Formulas
Range	Formula
D3:D6	D3	=IF(AND([@[extension date]]>TODAY()=TRUE,OR(XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="A/LP/PMP/PM Approved",XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="PMO Approved")=TRUE),"completed", IF(AND([@[extension date]]>TODAY()=FALSE,OR(XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="A/LP/PMP/PM Approved",XLOOKUP([@BU],AI_status[BU],AI_status[Status],"no data",0)="PMO Approved")=TRUE),"completed","delinquent"))

When i paste the formula is not doing nothing all i see is the formula

 

[cyrilbrd]

Well noted. What version of excel are you using?

 

[cyrilbrd]

Sorry I could not see the version on my phone.
Please make sure that the equal sign is there, no ' inserted prior to the equal sign.

Also please check this:

• Select the cell.
• Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.")
• Delete the "=" at the beginning of your formula, and hit Enter.
• Insert the "=" back in the formula at the beginning.

And

 

[luisitocarrion1900]

Sorry I could not see the version on my phone.
Please make sure that the equal sign is there, no ' inserted prior to the equal sign.
View attachment 7790
Also please check this:

• Select the cell.
• Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.")
• Delete the "=" at the beginning of your formula, and hit Enter.
• Insert the "=" back in the formula at the beginning.

And
View attachment 7789

was this meant for me

 

[cyrilbrd]

yes, you are using 365, the formula should work.
please kindly check the suggested fixes first.