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.

 

[Marcelo Branco]

Assuming the table has a column named Job, maybe something like this

First criteria
=IF(COUNTIFS(DataTable[Job],H2,DataTable[File Delivered],"<>",DataTable[Ready for Review],"YES"),"Complete","other conditions")

For the other two criteria replace "other conditions" by similar COUNTIFS formulas.

Hope this helps

M.

 

[dave2018]

Can u please upload a sample spreadsheet with some samples, and with the result that you want...
that would make it (a bit) easier to understand

 

[mrsbrannon]

Can u please upload a sample spreadsheet with some samples, and with the result that you want...
that would make it (a bit) easier to understand

I couldn't find a place to upload on this forum. I know some of the others offer it, but I don't think this one does.

 

[mrsbrannon]

Assuming the table has a column named Job, maybe something like this

First criteria
=IF(COUNTIFS(DataTable[Job],H2,DataTable[File Delivered],"<>",DataTable[Ready for Review],"YES"),"Complete","other conditions")

For the other two criteria replace "other conditions" by similar COUNTIFS formulas.

Hope this helps

M.

Do I not need to do some sort of an INDEX/MATCH since I'm looking from one sheet at another?

 

[markmzz]

Hi!

Another way.

In H2 and copy down:

=IF(OR(
AND(SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","Read for Review"},$A$1:$E$1,0),0)={"YES","YES"}))=2,VLOOKUP(G2,$A$2:$E$8,MATCH("File Delivered",$A$1:$E$1,0),0)<>""),
SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","File Delivered","Read for Review","Status"},$A$1:$E$1,0),0)={"YES","","NO","Cancelled"}))=4),"Complete",
IF(SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","File Delivered","Read for Review"},$A$1:$E$1,0),0)={"YES","",""}))=3,"WIP",""))

	A	B	C	D	E	F	G	H	I
1	Job Number	Most Recent	File Delivered	Read for Review	Status		Job Number	Result
2	JN0001	YES	Text0001	YES	Cancelled		JN0001	Complete
3	JN0002	NO		NO	Cancelled		JN0002
4	JN0003	YES		NO	Cancelled		JN0003	Complete
5	JN0004	NO		NO	Cancelled		JN0004
6	JN0005	YES			Cancelled		JN0005	WIP
7	JN0006	YES	Text0006	YES			JN0006	Complete
8	JN0007	YES					JN0007	WIP
9
***	************	************	**************	****************	***********	***	************	**********	**********

<tbody>
</tbody>

Markmzz

 

[mrsbrannon]

Another way:

=IF(OR(
AND(SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","Read for Review"},$A$1:$E$1,0),0)={"YES","YES"}))=2,VLOOKUP(G2,$A$2:$E$8,MATCH("File Delivered",$A$1:$E$1,0),0)<>""),
SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","File Delivered","Read for Review","Status"},$A$1:$E$1,0),0)={"YES","","NO","Cancelled"}))=4),"Complete",
IF(SUMPRODUCT(--(VLOOKUP(G2,$A$2:$E$8,MATCH({"Most Recent","File Delivered","Read for Review"},$A$1:$E$1,0),0)={"YES","",""}))=3,"WIP",""))

	A	B	C	D	E	F	G	H	I
1	Job Number	Most Recent	File Delivered	Read for Review	Status		Job Number	Result
2	JN0001	YES	Text0001	YES	Cancelled		JN0001	Complete
3	JN0002	NO		NO	Cancelled		JN0002
4	JN0003	YES		NO	Cancelled		JN0003	Complete
5	JN0004	NO		NO	Cancelled		JN0004
6	JN0005	YES			Cancelled		JN0005	WIP
7	JN0006	YES	Text0006	YES			JN0006	Complete
8	JN0007	YES					JN0007	WIP
9
***	************	************	**************	****************	***********	***	************	**********	**********

<tbody>
</tbody>

Markmzz

I have to use INDEX/MATCH vs. VLOOKUP because the job number column is in the middle of all the others and I can't relocate it.

 

[Marcelo Branco]

Do I not need to do some sort of an INDEX/MATCH since I'm looking from one sheet at another?

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

M.

 

[markmzz]

I have to use INDEX/MATCH vs. VLOOKUP because the job number column is in the middle of all the others and I can't relocate it.

Ok,

Try this small modification:

=IF(OR
(AND(SUMPRODUCT(--(T(OFFSET($A$1:$E$1,MATCH(G2,$C$2:$C$8,0),MATCH({"Most Recent","Read for Review"},$A$1:$E$1,0)-1))={"YES","YES"}))=2,INDEX($A$2:$E$8,MATCH(G2,$C$2:$C$8,0),MATCH("File Delivered",$A$1:$E$1,0))<>""),
SUMPRODUCT(--(T(OFFSET($A$1:$E$1,MATCH(G2,$C$2:$C$8,0),MATCH({"Most Recent","File Delivered","Read 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","Read for Review"},$A$1:$E$1,0)-1))={"YES","",""}))=3,"WIP",""))

	A	B	C	D	E	F	G	H
1	Most Recent	File Delivered	Job Number	Read for Review	Status		Job Number	Result
2	YES	Text0001	JN0001	YES	Cancelled		JN0001	Complete
3	NO		JN0002	NO	Cancelled		JN0002
4	YES		JN0003	NO	Cancelled		JN0003	Complete
5	NO		JN0004	NO	Cancelled		JN0004
6	YES		JN0005		Cancelled		JN0005	WIP
7	YES	Text0006	JN0006	YES			JN0006	Complete
8	YES		JN0007				JN0007	WIP
9
***	************	**************	**************	****************	***********	***	************	**********

<tbody>
</tbody>

Markmz

 

[Marcelo Branco]

Using data sample provided by markmzz

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Job Number​	Most Recent​	File Delivered​	Ready for Review​	Status​			Job Number​	Result​
2​	JN0001​	YES​	Text0001​	YES​	Cancelled​			JN0001​	Complete​
3​	JN0002​	NO​		NO​	Cancelled​			JN0002​
4​	JN0003​	YES​		NO​	Cancelled​			JN0003​	Complete​
5​	JN0004​	NO​		NO​	Cancelled​			JN0004​
6​	JN0005​	YES​			Cancelled​			JN0005​	WIP​
7​	JN0006​	YES​	Text0006​	YES​				JN0006​	Complete​
8​	JN0007​	YES​						JN0007​	WIP​

DataTable in A1:E8

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.