Trouble with IF(OR Formula

[Fanwood]

Hi Guys, having a little trouble with an if(or formula. It isn't returning anything, just the dreaded #VALUE!. Is it something wrong with the formula? Am I writing it incorrectly?

=IF(OR(A2="CUSTOMER REQUESTED REVIEW",A2="ACCEPTED",A2="ASSIGNED TO PROVIDER",A2="PROVIDER ACCEPTS WITH CONDITION",A2="EXCEPTION - DUPLICATE ORDER",A2="EXCEPTION - MANUAL ASSIGN", A2="Accepted by Vendor",A2="Accepted by Vendor with Conditions","Action Required",A2="Assigned to Vendor",A2="Being Typed",A2="Cancellation Pending",A2="Declined by Vendor",A2="Inspected",A2="Scheduled",A2="Unassigned",A2="Waiting for Acceptance"),"In Process",IF(OR(A2="WHOLESALE - WAITING FOR APPRAISAL",A2= "EXCEPTION – PROCESSOR",A2="Pending Vendor Onboarding",A2="Action Required",A2="Vendor Search Complete-Unsuccessful"),"Expedited Recruiting",IF(OR(A2="DRAFT NOT ACCEPTABLE",A2="ESCALATED REVIEW",A2="EXCEPTION - REVIEWER",A2="READY FOR REVIEW",A2="Client Request for Revisions/Addenda",A2="In Review",A2="QC Level-1 Approval Suggested",A2="QC Lelvel-1 Revisions Suggested",A2="Ready for Review",A2="Request for Revisions/Addenda"),”In QC”,IF(OR(A2=”Order Completed”,A2=”CANCELLED - WITH FEE DECLINED”,A2=”DECLINED”,A2="APPROVED - AS IS",A2="Completed"),"Order Completed",IF(OR(A2="ON HOLD", A2="ON HOLD - WAITING FOR APPROVAL",A2="Awaiting Information from Client"),"On Hold",IF(OR(A2="CANCELLED - BY REQUEST",A2="CANCELLED - DUPLICATE REQUEST",A2="Cancelled - Duplicate Order/Request",A2="Cancelled"),"Remove","Remove"))))))

 

[Jonmo1]

You left off the A2= of one of the criteria in the first OR

=IF(OR(A2="CUSTOMER REQUESTED REVIEW",A2="ACCEPTED",A2="ASSIGNED TO PROVIDER",A2="PROVIDER ACCEPTS WITH CONDITION",A2="EXCEPTION - DUPLICATE ORDER",A2="EXCEPTION - MANUAL ASSIGN", A2="Accepted by Vendor",A2="Accepted by Vendor with Conditions",A2="Action Required",A2="Assigned to Vendor",A2="Being Typed",A2="Cancellation Pending",A2="Declined by Vendor",A2="Inspected",A2="Scheduled",A2="Unassigned",A2="Waiting for Acceptance"),"In Process",IF(OR(A2="WHOLESALE - WAITING FOR APPRAISAL",A2= "EXCEPTION – PROCESSOR",A2="Pending Vendor Onboarding",A2="Action Required",A2="Vendor Search Complete-Unsuccessful"),"Expedited Recruiting",IF(OR(A2="DRAFT NOT ACCEPTABLE",A2="ESCALATED REVIEW",A2="EXCEPTION - REVIEWER",A2="READY FOR REVIEW",A2="Client Request for Revisions/Addenda",A2="In Review",A2="QC Level-1 Approval Suggested",A2="QC Lelvel-1 Revisions Suggested",A2="Ready for Review",A2="Request for Revisions/Addenda"),”In QC”,IF(OR(A2=”Order Completed”,A2=”CANCELLED - WITH FEE DECLINED”,A2=”DECLINED”,A2="APPROVED - AS IS",A2="Completed"),"Order Completed",IF(OR(A2="ON HOLD", A2="ON HOLD - WAITING FOR APPROVAL",A2="Awaiting Information from Client"),"On Hold",IF(OR(A2="CANCELLED - BY REQUEST",A2="CANCELLED - DUPLICATE REQUEST",A2="Cancelled - Duplicate Order/Request",A2="Cancelled"),"Remove","Remove"))))))

 

[Fanwood]

Thanks for your Hawk eyes, now it gives me a #NAME? Error.....

You left off the A2= of one of the criteria in the first OR

=IF(OR(A2="CUSTOMER REQUESTED REVIEW",A2="ACCEPTED",A2="ASSIGNED TO PROVIDER",A2="PROVIDER ACCEPTS WITH CONDITION",A2="EXCEPTION - DUPLICATE ORDER",A2="EXCEPTION - MANUAL ASSIGN", A2="Accepted by Vendor",A2="Accepted by Vendor with Conditions",A2="Action Required",A2="Assigned to Vendor",A2="Being Typed",A2="Cancellation Pending",A2="Declined by Vendor",A2="Inspected",A2="Scheduled",A2="Unassigned",A2="Waiting for Acceptance"),"In Process",IF(OR(A2="WHOLESALE - WAITING FOR APPRAISAL",A2= "EXCEPTION – PROCESSOR",A2="Pending Vendor Onboarding",A2="Action Required",A2="Vendor Search Complete-Unsuccessful"),"Expedited Recruiting",IF(OR(A2="DRAFT NOT ACCEPTABLE",A2="ESCALATED REVIEW",A2="EXCEPTION - REVIEWER",A2="READY FOR REVIEW",A2="Client Request for Revisions/Addenda",A2="In Review",A2="QC Level-1 Approval Suggested",A2="QC Lelvel-1 Revisions Suggested",A2="Ready for Review",A2="Request for Revisions/Addenda"),”In QC”,IF(OR(A2=”Order Completed”,A2=”CANCELLED - WITH FEE DECLINED”,A2=”DECLINED”,A2="APPROVED - AS IS",A2="Completed"),"Order Completed",IF(OR(A2="ON HOLD", A2="ON HOLD - WAITING FOR APPROVAL",A2="Awaiting Information from Client"),"On Hold",IF(OR(A2="CANCELLED - BY REQUEST",A2="CANCELLED - DUPLICATE REQUEST",A2="Cancelled - Duplicate Order/Request",A2="Cancelled"),"Remove","Remove"))))))

 

[Jonmo1]

OK..

I really think you'd be better off with a different approach, probably a simple vlookup.

Put all those text strings into a range of cells, say G1:G20 or whatever.
And put the corresponding response for each of those values in H1:H20

Then a simple
=VLOOKUP(A2,G1:H20,2,FALSE) will work brilliantly.

This will also make it much easier to adjust the formula as needed.

 

[Fanwood]

updating my response, I realize what you are saying now but adding addiotnal data to reference from seems to defeat the purpose.

OK..

I really think you'd be better off with a different approach, probably a simple vlookup.

Put all those text strings into a range of cells, say G1:G20 or whatever.
And put the corresponding response for each of those values in H1:H20

Then a simple
=VLOOKUP(A2,G1:H20,2,FALSE) will work brilliantly.

This will also make it much easier to adjust the formula as needed.

 

[Jonmo1]

I'm suggesting that you 'create' a new range containing all the text strings and their corresponding values.

Example

Excel Workbook
	G	H
1	Accepted by Vendor	In Process
2	Assigned	In Process
3	Scheduled	In Process
4	Wholesale - Waiting For Appraisal	Expedited Recruiting
5	Exception - Processor	Expedited Recruiting
6	Pending Vendor Onboarding	Expedited Recruiting
7	Action Required	Expedited Recruiting
Sheet1

Excel Workbook
	A	B
2	Assigned	In Process
3	Exception - Processor	Expedited Recruiting
4	Action Required	Expedited Recruiting
5	Scheduled	In Process
Sheet1

 

[Fanwood]

I understand what you mean, I just didn't want to add any additional, unnecessary data to an already very large report. But if this is my only route then so be it. Thank you for your assistance Jonmo1. I appreciate your help

 

[Jonmo1]

I'm not saying it's the 'only' option.

You're more than welcome to use the longwinded nested if/or function if you like.

But in my opinion, Excel gives you 256 columns and 65536 rows (more in xl2007+).
Might as well use 'a few' of them.

It is so much easier to troubleshoot a Vlookup than a nested if/or that is over 1300 characters long.


I did find that you have some strange quotation marks in there.
” instead of "

I did a find/replace to fix it for you.


=IF(OR(A2="CUSTOMER REQUESTED REVIEW",A2="ACCEPTED",A2="ASSIGNED TO PROVIDER",A2="PROVIDER ACCEPTS WITH CONDITION",A2="EXCEPTION - DUPLICATE ORDER",A2="EXCEPTION - MANUAL ASSIGN", A2="Accepted by Vendor",A2="Accepted by Vendor with Conditions",A2="Action Required",A2="Assigned to Vendor",A2="Being Typed",A2="Cancellation Pending",A2="Declined by Vendor",A2="Inspected",A2="Scheduled",A2="Unassigned",A2="Waiting for Acceptance"),"In Process",IF(OR(A2="WHOLESALE - WAITING FOR APPRAISAL",A2= "EXCEPTION – PROCESSOR",A2="Pending Vendor Onboarding",A2="Action Required",A2="Vendor Search Complete-Unsuccessful"),"Expedited Recruiting",IF(OR(A2="DRAFT NOT ACCEPTABLE",A2="ESCALATED REVIEW",A2="EXCEPTION - REVIEWER",A2="READY FOR REVIEW",A2="Client Request for Revisions/Addenda",A2="In Review",A2="QC Level-1 Approval Suggested",A2="QC Lelvel-1 Revisions Suggested",A2="Ready for Review",A2="Request for Revisions/Addenda"),"In QC",IF(OR(A2="Order Completed",A2="CANCELLED - WITH FEE DECLINED",A2="DECLINED",A2="APPROVED - AS IS",A2="Completed"),"Order Completed",IF(OR(A2="ON HOLD", A2="ON HOLD - WAITING FOR APPROVAL",A2="Awaiting Information from Client"),"On Hold",IF(OR(A2="CANCELLED - BY REQUEST",A2="CANCELLED - DUPLICATE REQUEST",A2="Cancelled - Duplicate Order/Request",A2="Cancelled"),"Remove","Remove"))))))

 

[Fanwood]

Interesting, thank you for the advice.

I'm not saying it's the 'only' option.

You're more than welcome to use the longwinded nested if/or function if you like.

But in my opinion, Excel gives you 256 columns and 65536 rows (more in xl2007+).
Might as well use 'a few' of them.

It is so much easier to troubleshoot a Vlookup than a nested if/or that is over 1300 characters long.


I did find that you have some strange quotation marks in there.
” instead of "

I did a find/replace to fix it for you.


=IF(OR(A2="CUSTOMER REQUESTED REVIEW",A2="ACCEPTED",A2="ASSIGNED TO PROVIDER",A2="PROVIDER ACCEPTS WITH CONDITION",A2="EXCEPTION - DUPLICATE ORDER",A2="EXCEPTION - MANUAL ASSIGN", A2="Accepted by Vendor",A2="Accepted by Vendor with Conditions",A2="Action Required",A2="Assigned to Vendor",A2="Being Typed",A2="Cancellation Pending",A2="Declined by Vendor",A2="Inspected",A2="Scheduled",A2="Unassigned",A2="Waiting for Acceptance"),"In Process",IF(OR(A2="WHOLESALE - WAITING FOR APPRAISAL",A2= "EXCEPTION – PROCESSOR",A2="Pending Vendor Onboarding",A2="Action Required",A2="Vendor Search Complete-Unsuccessful"),"Expedited Recruiting",IF(OR(A2="DRAFT NOT ACCEPTABLE",A2="ESCALATED REVIEW",A2="EXCEPTION - REVIEWER",A2="READY FOR REVIEW",A2="Client Request for Revisions/Addenda",A2="In Review",A2="QC Level-1 Approval Suggested",A2="QC Lelvel-1 Revisions Suggested",A2="Ready for Review",A2="Request for Revisions/Addenda"),"In QC",IF(OR(A2="Order Completed",A2="CANCELLED - WITH FEE DECLINED",A2="DECLINED",A2="APPROVED - AS IS",A2="Completed"),"Order Completed",IF(OR(A2="ON HOLD", A2="ON HOLD - WAITING FOR APPROVAL",A2="Awaiting Information from Client"),"On Hold",IF(OR(A2="CANCELLED - BY REQUEST",A2="CANCELLED - DUPLICATE REQUEST",A2="Cancelled - Duplicate Order/Request",A2="Cancelled"),"Remove","Remove"))))))

 

[Jonmo1]

You're welcome.