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

  • BullandKhmer
    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

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


  • BullandKhmer
    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
    Josh Caldwell ✭✭
    edited 01/31/22

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!