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.

 

[mrsbrannon]

brannon

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

M.

Thanks, Marcelo! The first one you suggested appears to be working! I will get put into action next week so if there are any issues, I should find out then, but for now, it looks great! Thank you for your help!!

 

[Marcelo Branco]

You are welcome. Glad to help

M.