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

M

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
dave2018 said:
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
Click to expand...


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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...


Do I not need to do some sort of an INDEX/MATCH since I'm looking from one sheet at another?
 
Upvote 0
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",""))


[TABLE="class: grid, width: 797"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Job Number[/TD]
[TD]Most Recent[/TD]
[TD]File Delivered[/TD]
[TD]Read for Review[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Job Number[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JN0001[/TD]
[TD]YES[/TD]
[TD]Text0001[/TD]
[TD]YES[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0001[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JN0002[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JN0003[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0003[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]JN0004[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]JN0005[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0005[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]JN0006[/TD]
[TD]YES[/TD]
[TD]Text0006[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0006[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]JN0007[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0007[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]************[/TD]
[TD]************[/TD]
[TD]**************[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[TD]************[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Last edited:
Upvote 0
markmzz said:
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",""))


[TABLE="class: grid, width: 797"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Job Number[/TD]
[TD]Most Recent[/TD]
[TD]File Delivered[/TD]
[TD]Read for Review[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Job Number[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JN0001[/TD]
[TD]YES[/TD]
[TD]Text0001[/TD]
[TD]YES[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0001[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JN0002[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JN0003[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0003[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]JN0004[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]JN0005[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0005[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]JN0006[/TD]
[TD]YES[/TD]
[TD]Text0006[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0006[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]JN0007[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0007[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]************[/TD]
[TD]************[/TD]
[TD]**************[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[TD]************[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
Click to expand...


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. :(
 
Upvote 0
mrsbrannon said:
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. :(
Click to expand...

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",""))

[TABLE="class: grid, width: 720"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Most Recent[/TD]
[TD]File Delivered[/TD]
[TD]Job Number[/TD]
[TD]Read for Review[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Job Number[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]YES[/TD]
[TD]Text0001[/TD]
[TD]JN0001[/TD]
[TD]YES[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0001[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]JN0002[/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]JN0003[/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0003[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]JN0004[/TD]
[TD]NO[/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]JN0005[/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]JN0005[/TD]
[TD]WIP[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]YES[/TD]
[TD]Text0006[/TD]
[TD]JN0006[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0006[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]JN0007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JN0007[/TD]
[TD]WIP[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]************[/TD]
[TD]**************[/TD]
[TD]**************[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[TD]************[/TD]
[TD]**********[/TD]
[/TR]
</tbody>[/TABLE]

Markmz
 
Last edited:
Upvote 0
Using data sample provided by markmzz


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Job Number​
[/td][td]
Most Recent​
[/td][td]
File Delivered​
[/td][td]
Ready for Review​
[/td][td]
Status​
[/td][td][/td][td][/td][td]
Job Number​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
JN0001​
[/td][td]
YES​
[/td][td]
Text0001​
[/td][td]
YES​
[/td][td]
Cancelled​
[/td][td][/td][td][/td][td]
JN0001​
[/td][td]
Complete​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
JN0002​
[/td][td]
NO​
[/td][td][/td][td]
NO​
[/td][td]
Cancelled​
[/td][td][/td][td][/td][td]
JN0002​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
JN0003​
[/td][td]
YES​
[/td][td][/td][td]
NO​
[/td][td]
Cancelled​
[/td][td][/td][td][/td][td]
JN0003​
[/td][td]
Complete​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
JN0004​
[/td][td]
NO​
[/td][td][/td][td]
NO​
[/td][td]
Cancelled​
[/td][td][/td][td][/td][td]
JN0004​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
JN0005​
[/td][td]
YES​
[/td][td][/td][td][/td][td]
Cancelled​
[/td][td][/td][td][/td][td]
JN0005​
[/td][td]
WIP​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
JN0006​
[/td][td]
YES​
[/td][td]
Text0006​
[/td][td]
YES​
[/td][td][/td][td][/td][td][/td][td]
JN0006​
[/td][td]
Complete​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
JN0007​
[/td][td]
YES​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
JN0007​
[/td][td]
WIP​
[/td][/tr]
[/table]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
MATCH and INDEX help
Replies
3
Views
868
etaf
etaf
M
  • Question Question
A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.
Replies
2
Views
152
MARZIOTULLIO
M
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
169
jbennett01
J
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
297
henrik2h
H
A
  • Question Question
Index match match not using second set of match criteria
Replies
3
Views
171
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top