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

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.
 
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!!
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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