IF formula in a Matrix

[olympiac]

Here is the scenario:

In sheet(1), I have got the following table where the values in the grid are the result of a formula (IF forumla):

A1 EMPLOYEE B1 PRODUCT_1 C1 PRODUCT_2 D1 PRODUCT_3
A2 name 1 B2 order C2 completed D2 not required
A3 name 2 B3 completd C3 completed D3 order
A4 name 3 B4 order C4 not required D4 completed


In sheet(2), I have got a tracker like this:

A1 EMPLOYEE B1 PRODUCT_type C1 status
A2 name 1 B2 Product_1 C2 completed
A3 name 3


I need a formula that checks the following scenarios:

If employee in sheet(1) is listed in sheet(2) with the status "completed" then the value in sheet(1) is "completed"

if employee in sheet(1) is listed in sheet(2) with the status < >"completed" then nothing (I keep the result of my first IF Formula)

if employee in sheet(1) is not listed in sheet(2) then nothing


Can you assist, please?

 

[DebugGalpin]

Here is the scenario:

In sheet(1), I have got the following table where the values in the grid are the result of a formula (IF forumla):

A1 EMPLOYEE B1 PRODUCT_1 C1 PRODUCT_2 D1 PRODUCT_3
A2 name 1 B2 order C2 completed D2 not required
A3 name 2 B3 completd C3 completed D3 order
A4 name 3 B4 order C4 not required D4 completed


In sheet(2), I have got a tracker like this:

A1 EMPLOYEE B1 PRODUCT_type C1 status
A2 name 1 B2 Product_1 C2 completed
A3 name 3


I need a formula that checks the following scenarios:

If employee in sheet(1) is listed in sheet(2) with the status "completed" then the value in sheet(1) is "completed"

if employee in sheet(1) is listed in sheet(2) with the status < >"completed" then nothing (I keep the result of my first IF Formula)

if employee in sheet(1) is not listed in sheet(2) then nothing


Can you assist, please?

1) =iferror(if(vlookup(Sheet1!$A2,Sheet2!A:C,3,0)="completed","completed",""),"")

Looks up employee in sheet 2 if its there returns column C in sheet 2. If it equals complete, if not then nothing and if there is an error nothing

Should work fine

2)

 

[olympiac]

Interesting. I think, it should work. I'll try it.
Thanks

 

[olympiac]

I tried your formula but it doesn't work. Can you review it plase.

Sheet(1) is called Summary
Sheet(2) is called Tracker

If the employee name is not in Sheet(Tracker) then the formula in Sheet(Sumamry) is:

=IF(ISERROR('Team Static'!Z3),IF(ISREF('Team Static'!Z3),IF('Team Static'!DE3>0,"Req","No"),IF(AND('Team Static'!Z3>0,'Team Static'!DE3>0),"Yes",IF(AND('Team Static'!Z3=0,'Team Static'!DE3>0),"Req",IF(AND('Team Static'!Z3>0,'Team Static'!DE3=0),"Del","No")))),IF(AND('Team Static'!Z3>0,'Team Static'!DE3>0),"Yes",IF(AND('Team Static'!Z3=0,'Team Static'!DE3>0),"Req",IF(AND('Team Static'!Z3>0,'Team Static'!DE3=0),"Del","No"))))

If the employe name is in Sheet(Tracker) with the status "completed" in column C then the result in Sheet(Summary) should be completed otherwise the result will be the one from the formula above.

 

[DebugGalpin]

So can "Completed" be in any column but you just want to make sure there is a completed there? As the vlookup was only checking column C which could have been the problem, and are the names exactly the same e.g. "Terry Dixon" in Sheet 1 is "Terry Dixon" in Sheet 2

 

[olympiac]

I have solved the problem. I came up with something like that. It works. Thanks for you support

=IF(ISERROR(IF(AND(VLOOKUP($B$520:$B$533,Tracker!$A$1:$E$8,4,0)="completed",VLOOKUP(C$519,Tracker!$B$1:$E$8,1,FALSE)=C$519),VLOOKUP($B$520:$B$533,Tracker!$A$1:$E$8,5,0),"Other FORMULA")),"0",IF(AND(VLOOKUP($B$520:$B$533,Tracker!$A$1:$E$8,4,0)="completed",VLOOKUP(C$519,Tracker!$B$1:$E$8,1,FALSE)=C$519),VLOOKUP($B$520:$B$533,Tracker!$A$1:$E$8,5,0),"other FORMULA"))