Help with returning Yes value if multiple criteria is met
I am trying to identify which RED customers from one Sheet 1 have already been added to our CEM (Customer Escalation Master) Sheet 2, so I don't have to manually spot check between both.
I created a new Column called, "On Escalation Master?" and want to return a Yes or No value.
I am getting #UNPARSEABLE error when I try to VLOOKUP the Customer Code in the current Sheet 1 to the reference column in the CEM_Customer Code reference Sheet 2. First, I want to know if the Customer Code in the current Sheet 1 exists already in the other reference Sheet 2.
=IFERROR(VLOOKUP([Customer Code]@row, ={CEM_Customer Code}, 0, TRUE) "Yes", "No")
Secondly, once above formula is corrected and criteria met (Customer Code exists in both), then I want to know if the "Status" column in Sheet 2 = "Not Started" or "In Progress" or "Pending Closure" (basically anything NOT "Completed" or "Terminated")
If both criteria is met value = Yes, else No.
I want to filter value "No" in Sheet 1 then sort the RED customers so I know which ones need to be added to Sheet 2 (Escalation Project Tracking) each week. Any help is greatly appreciated.
Answers
-
Yeah, sharing some screen shots might help.
But I think for the first part:
IF(VLOOKUP([Customer Code]@row, (COLUMN YOU WANT TO VLOOKUP IN),1,false)={CEM_Customer Code},"Yes","No")
Not sure really, share screen shots
-
@BullandKhmer - Here's the screenshot of Sheet 1 (data source) and Sheet 2 (reference lookup). I need to know if the Customer Code in Sheet 1 exists already in Sheet 2 (Sheet 2 also contains the Status) so if Yes (first criteria met), then next criteria is if status = Not Started, In Progress, or Pending Closure then return the value "Yes"
Basically, trying to report any new entries in Sheet 1 that has not already been added/escalated to Sheet 2 and worked (marked Completed) to validate using Customer Code as the unique identifier and Status check. Appreciate any help with the formula.
-
=IF(ISERROR(INDEX({Sheet1_Ref1},MATCH([Customer Code]@row,{Sheet1_Ref1},0))),"No",IF(NOT(OR([Status]@row="Not Started",[Status]@row="In Progress",[Status]@row="Pending Closure")),"No","Yes")
You will need to change {Sheet1_Ref1} to the correct reference.
100% certain there is a more concise solution, but this will work.
-
@BullandKhmer - Thanks for the quick reply! I got an UNPARSEABLE error when I try the formula on Sheet 1. It does work when I put the formula in Sheet 2, but I need the Yes/No validation in Sheet 1 (intake) to make a decision if it warrants an entry into Sheet 2 (project work).
Here's some additional screenshots.
Sheet 1_Customer Risk and Segmentation (Intake/Validation) -- need formula on Sheet 1 (Customer Code) to reference Sheet 2 (Customer Code and Status)
Sheet 2_CEM_Customer Code (Customer Escalation Master - Project Assignments)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!