IF/AND/OR + INDEX/MATCH, and another IF

[mrsbrannon]

Hello all,

Looking for a really long formula with multiple criteria needing to be met. I hope this is doable. Lol. Here is what I need it to do.

1. Look up a job number and find the matching number/row on the data sheet.
2. Check 3 columns for specific data

• The "Most Recent" column to = "YES"
• The "File Delivered" column to = any text (if there is anything at all in this cell)
• The "Ready for Review" column to = "YES"

3. IF all three of these criteria are met, return "Complete"

NEXT

1. Look up a job number and find the matching number/row on the data sheet
2. Check 4 columns for specific data

• The "Most Recent" column to = "YES"
• The "File Delivered" column to be BLANK
• The "Ready for Review" column to = "NO"
• The "Status" column to = "Cancelled"

3. IF all four of these criteria are met, also return "Complete"

NEXT

1. Look up a job number and find the matching number/row on the data sheet
2. Check 3 columns for specific data

• The "Most Recent" column to = "YES"
• The "File Delivered" column to be BLANK
• The "Ready for Review" column to be BLANK

3. IF all three of these criteria are met, return "WIP"

I have tried slowly building the formula using the first criteria only and was planning to build from there, but it's not returning accurate info. Here is what I tried.

=IF(AND(INDEX(DataTable,(MATCH(H2,DataTable,0)),),(DataTable[Most Recent]="YES"),(DataTable[Ready for Review]="YES"),(ISTEXT(DataTable[File Delivered]),"Complete")

I feel like I'm headed in the right direction but not sure.

Thanks in advance for any help you're able to provide.

 

[markmzz]

If i'm understanding correctly what you are trying to do COUNTIFS should work. Have you tried the formula in post 2?

M.

Hi Marcelo!


Your formula works too (with my data):

=IF(COUNTIFS($C$2:$C$8,G2,$B$2:$B$8,"<>",$D$2:$D$8,"YES")+
COUNTIFS($C$2:$C$8,G2,$A$2:$A$8,"YES",$B$2:$B$8,"",$D$2:$D$8,"NO",$E$2:$E$8,"Cancelled"),"Complete",
IF(COUNTIFS($C$2:$C$8,G2,$A$2:$A$8,"YES",$B$2:$B$8,"",$D$2:$D$8,""),"WIP","other conditions"))

Markmzz

 

[Marcelo Branco]

Hi Marcelo!


Your formula works too (with my data):

=IF(COUNTIFS($C$2:$C$8,G2,$B$2:$B$8,"<>",$D$2:$D$8,"YES")+
COUNTIFS($C$2:$C$8,G2,$A$2:$A$8,"YES",$B$2:$B$8,"",$D$2:$D$8,"NO",$E$2:$E$8,"Cancelled"),"Complete",
IF(COUNTIFS($C$2:$C$8,G2,$A$2:$A$8,"YES",$B$2:$B$8,"",$D$2:$D$8,""),"WIP","other conditions"))

Markmzz

Hi Mark.

Yes, it works and, as you know, using COUNTIFS the position of the column Job Number is irrelevant.

Thank you for testing!

M.

 

[markmzz]

Marcelo,

Here is a version for Excel 2003:

=IF(SUMPRODUCT(--($C$2:$C$8=G2),--($A$2:$A$8="YES"),--($D$2:$D$8="YES"),--(B2:B8<>""))+
SUMPRODUCT(--($C$2:$C$8=G2),--($A$2:$A$8="YES"),--($D$2:$D$8="NO"),--(B2:B8=""),--($E$2:$E$8="Cancelled")),"Complete",
IF(SUMPRODUCT(--($C$2:$C$8=G2),--($A$2:$A$8="YES"),--($D$2:$D$8=""),--(B2:B8="")),"WIP",""))

Markmzz

 

[Marcelo Branco]

Mark,

Yes, your version for xl 2003 looks fine to me.

Remarks:
1. I like the addition of the two fiirst set of conditions to get "Complete"
=IF(Sumproduct(conditions1)+Sumproduct(conditions2),"Complete",...)

2. In your data sample there is a typo in a header (issue when using a Table)
The header Read for Review should be Ready for Review

M.

 

[mrsbrannon]

If i'm understanding correctly what you are trying to do COUNTIFS should work. Have you tried the formula in post 2?

M.

I have not yet, but I will. I was mainly asking to increase my own knowledge of how INDEX/MATCH and COUNTIF works (I was under the impression that you had to either use VLOOKUP or INDEX/MATCH to find items on another worksheet). Wasn't trying to say it wouldn't work as you formulated. Once I test them out, I'll let you know. Thanks!

 

[Marcelo Branco]

I have not yet, but I will. I was mainly asking to increase my own knowledge of how INDEX/MATCH and COUNTIF works (I was under the impression that you had to either use VLOOKUP or INDEX/MATCH to find items on another worksheet). Wasn't trying to say it wouldn't work as you formulated. Once I test them out, I'll let you know. Thanks!

If you are using a Table i see no need of INDEX/MATCH or VLOOKUP.

This worked for me
Data in some sheet formatted as a table whose name is DataTable
On another sheet


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Job Number​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

JN0001​

[/TD]
[TD]

Complete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

JN0002​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

JN0003​

[/TD]
[TD]

Complete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

JN0004​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

JN0005​

[/TD]
[TD]

WIP​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

JN0006​

[/TD]
[TD]

Complete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

JN0007​

[/TD]
[TD]

WIP​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in I2 copied down
=IF(COUNTIFS(DataTable[Job Number],H2,DataTable[File Delivered],"<>",DataTable[Ready for Review],"YES"),"Complete",IF(COUNTIFS(DataTable[Job Number],H2,DataTable[Most Recent],"YES",DataTable[File Delivered],"",DataTable[Ready for Review],"NO",DataTable[Status],"Cancelled"),"Complete",IF(COUNTIFS(DataTable[Job Number],H2,DataTable[File Delivered],"",DataTable[Ready for Review],""),"WIP","")))

M.

 

[markmzz]

Another version:

=IF(SUMPRODUCT(--($C$2:$C$8=G2),--($A$2:$A$8="YES"),--($D$2:$D$8="YES"),--($B$2:$B$8<>""))+
(SUMPRODUCT(($C$2:$C$8=G2)*($A$2:$B$8={"YES",""})*($D$2:$D$8="NO")*($E$2:$E$8="Cancelled"))=2),"Complete",
IF(SUMPRODUCT(($C$2:$C$8=G2)*($A$2:$B$8={"YES",""})*($D$2:$D$8=""))=2,"WIP",""))

=IF(SUMPRODUCT(--(DataTable[Job Number]=G2),--(DataTable[Most Recent]="YES"),--(DataTable[Read for Review]="YES"),--(DataTable[File Delivered]<>""))+
(SUMPRODUCT((DataTable[Job Number]=G2)*(DataTable[[Most Recent]:[File Delivered]]={"YES",""})*(DataTable[Read for Review]="NO")*(DataTable[Status]="Cancelled"))=2),"Complete",
IF(SUMPRODUCT((DataTable[Job Number]=G2)*(DataTable[[Most Recent]:[File Delivered]]={"YES",""})*(DataTable[Read for Review]=""))=2,"WIP",""))

Markmzz

 

[Marcelo Branco]

brannon

I have to leave now. Hope the solutions suggested by me and Mark works.
Good luck!

M.

 

[Marcelo Branco]

@Mark

Have you seen my Remark 2 in post 14?
The OP my face problems using your formula in post 17 caused by the misspell in the header Read for Review

M.

 

[markmzz]

@Mark

Have you seen my Remark 2 in post 14?
The OP my face problems using your formula in post 17 caused by the misspell in the header Read for Review

M.

First, thank you Marcelo for the help.

And now, here is two formulas (with Ready and not Read in the hearder):

=IF(SUMPRODUCT(--(DataTable[Job Number]=G2),--(DataTable[Most Recent]="YES"),--(DataTable[Ready for Review]="YES"),--(DataTable[File Delivered]<>""))+
(SUMPRODUCT((DataTable[Job Number]=G2)*(DataTable[[Most Recent]:[File Delivered]]={"YES",""})*(DataTable[Ready for Review]="NO")*(DataTable[Status]="Cancelled"))=2),"Complete",
IF(SUMPRODUCT((DataTable[Job Number]=G2)*(DataTable[[Most Recent]:[File Delivered]]={"YES",""})*(DataTable[Ready for Review]=""))=2,"WIP",""))

=IF(
AND(SUMPRODUCT(--(T(OFFSET($A$1:$E$1,MATCH(G2,$C$2:$C$8,0),MATCH({"Most Recent","Ready for Review"},$A$1:$E$1,0)-1))={"YES","YES"}))=2,INDEX($B$2:$B$8,MATCH(G2,$C$2:$C$8,0),)<>"")+
(SUMPRODUCT(--(T(OFFSET($A$1:$E$1,MATCH(G2,$C$2:$C$8,0),MATCH({"Most Recent","File Delivered","Ready for Review","Status"},$A$1:$E$1,0)-1))={"YES","","NO","Cancelled"}))=4),"Complete",
IF(SUMPRODUCT(--(T(OFFSET($A$1:$E$1,MATCH(G2,$C$2:$C$8,0),MATCH({"Most Recent","File Delivered","Ready for Review"},$A$1:$E$1,0)-1))={"YES","",""}))=3,"WIP",""))

Markmzz