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.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

BullandKhmer

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

Josh Caldwell

@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.

Customer Code Sheet 1.PNG

Customer Code Sheet 2.PNG

BullandKhmer

=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.

Josh Caldwell

@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)

Sheet 1_Formula Error.PNG

Sheet 2_Formula.PNG

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.