Need V-LOOKUP formula that looks for both text or numbers in different locations

Hi Everyone,

I need (I believe) a VLOOKUP formula that will look for certain text and numbers across a few different regions (columns).

PART 1 - The first portion of this formula would look in region L(4:29). If any single one of these cells are NOT equal to either "No Action Required" or CONTAINS "Waiting". If any of these are true the output is "YES", otherwise "NO".

PART 2 - Another portion of this same formula would look across regions B(31:36) AND O(31:36) to see if any single one of these cells DOES NOT equal "1" - If any of of these are true, the output is "YES", otherwise "NO"

If either PART 1 or PART 2 are true, then the output would be, "YES", otherwise, "NO"


I hope this makes sense. I have a column labelled "Action Required" that outputs various text telling me what I need to complete for that line. It might say, "Send Shipping Options to Customer" or "Create Sales Order" depending on whether I have completed other items or not. This formula will output a YES or NO on the project header that will tell me if there are any items at all that need my attention for that particular project.

The reason I did that is because I want the VLOOKUP looking for text that's telling me what I need to do - I have dozens of different text that might be outputted in the L(4:29) region, so its easier to check and see if there is nothing that needs to be done, rather than looking for things that do need to be done. Hope that makes sense.

I'll try this formula that you have given me and let you know how it works!

So the formula doesn't quite work though I'm not sure why. Can I Ask what are the use of the <>26 and <>12 in the equation?

None of these cells are ver blank, there's always at either text (for L4:L29) or 1 or 0 for the others. Not sure why it's not working. I switched the NO + YES at the end of the formula, but the equation is always outputting NO no matter what is in any of the cells.

Perfect! This formula works. I just copied it in and tried it! Thanks so much for helping with this.