IF function for several options

[JeninQC]

Hi,

I have a large and complex table in Excel where we need a quick and easy visual indicator in column A that will highlight if there are any issues that need to be checked.

Please see attached simplified table that I hope will help to demonstrate what I need to happen.

Column A is where I want the IF function
Column E will be entered manually, and if a "YES" is entered here, we want the site automatically blocked (this happens in Column K)
Columns F; G and I will be filled in manually by the team using the spreadsheet
Column K has a basic formula to automatically block a new site (until the site is checked and approved, then the yes in here will be deleted by the person making the approval), unless it is not a new site, but there is a reason to block it - then the YES would be entered manually.

Column M is a status (fed by other columns that I have removed, but I have left the different results that might show to let you see)

Basically, I would like the following to happen:
IF cell F2 shows YES, cell A2 should show nothing
If Cell G2 or I2, show either LR or HR, cell A2 should show CONDITIONAL
If cell K2 shows YES, then A2 should show BLOCKED
If cells F2 - K2 are all blank, then A2 should show the information in cell M2, however if there is no value in cell M2, it should show the word CHECK

I can figure most of it out, but it all goes wrong when I want it to check the range (F2:K2) and I think that it is because of the formula in column K, and the formula in Column M, and I cannot figure my head around it.

To assist on the table attached, I have entered comments with the values I would like to see in the cells in Column A

Any help is much appreciated.

Thank you

[PaulM100]

This should do:

=IF(F2="YES","",IF(OR(OR(G2="LR",G2="HR"),OR(I2="LR",I2="HR")),"CONDITIONAL",IF(K2="YES","BLOCKED",IF(AND(F2="",G2="",I2="",K2="",M2=""),"CHECK",IF(AND(F2="",G2="",I2="",K2=""),M2)))))

[samba_ravi]

May be the below one
a2=IF(F2="YES","",IF(OR(G2={"LR","HR"},I2={"LR","HR"}),"CONDITIONAL",IF(K2="YES","BLOCKED",IF(AND(F2="",K2=""),IF(M2="","CHECK",M2),""))))

Please Login or Register  to view this content.

[JeninQC]

Brilliant, thank you both for your help with this.