Help with Lookup Formula using IF CONTAINS Status In Progress/Not Started/Watch

Josh Caldwell

Hello,

We run a weekly Sales report of any new Yellow or Red risk customers, that gets added into an Escalation Master Tracker in Smartsheet. Master Tracker may contain matching "Customer Code" entries more than once (past entries marked "Completed" or "Terminated") and new entry status = "Not Started", "In Progress", or "Watch" (open escalation). Before it gets added to the Master Tracker, we use an Analysis Sheet as a backend table to feed other data, as well as Conditional Formatting used to show any changes or highlight entries that need to be added to the Tracker.

What I am trying to do on Analysis Sheet 1, column "Open Escalation", is return a value of "YES/NO" when matching "Customer Code" in Master Tracker Reference Sheet 2 (CEM Range_Risk Analysis) "Status" column = "Not Started", In Progress" or in "Watch" (YES). So I can filter by any with NO and add those to the Master Tracker for that week.

The problem I am running into is if there are multiple entries already on the Master Tracker, some already marked "Completed" and the logic stops there. When there are more than 1 entries though I need to know if one of them are "In Progress" and my current formula is returning the first lookup Status when "Customer Code" is a match, which is usually "Completed".

So, Analysis Sheet 1 results as value, "NO" Open Escalation, see example below for SWY has 2 entries marked "Completed" but one marked "In Progress" so it should say "YES" Open Escalation because the Status for at least 1/3 is already "In Progress" (or "Not Started" or "Watch").

Original Formula:

=IF(VLOOKUP([Customer Code]@row, {CEM Range_Risk Analysis}, 4, 0) = "In Progress", "Yes", "No")

Not sure if I should be using IF CONTAINS formula with INDEX/MATCH or INDEX/COLLECT? Any help would be greatly appreciated!

Analysis Sheet 1

Master Tracker Sheet 2 {CEM Range_Risk Analysis}

Josh Caldwell

Got it! Since I also have "Completed" and "Terminated" status to exclude, this seems to work specifying each open status by adding OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch")

=IF(COUNTIFS({CEM Risk_Customer Code}, [Customer Code]@row, {CEM Risk_Status}, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch")) > 0, "Yes", "No")

Thanks again!

Genevieve P.

Hi @Josh Caldwell

What I would do in this instance is actually use a COUNTIFS Function - then you can say If the COUNT of rows that have your Code and are "In Progress" is greater than 0 (so there's even one row that has "In Progress") then return "Yes", otherwise "No".

Try something like this:

=IF(COUNTIFS({Customer Code Column}, [Customer Code]@row, {Status Column}, "In Progress") > 0, "Yes", "No")

Cheers!

Genevieve

Josh Caldwell

@Genevieve P.

Thanks so much! This is a big step closer to what I was looking for!

How do you include the other two open statuses in the COUNTIFS formula below? I want it to return "YES" if it counts any of "Not Started" "In Progress" or "Watch" (these are progressions before "Completed" so any one of them will count)

=IF(COUNTIFS({CEM Risk_Customer Code}, [Customer Code]@row, {CEM Risk_Status}, "In Progress") > 0, "Yes", "No")

Genevieve P.

Hi @Josh Caldwell

Oh I see! Apologies I missed that in the original question.

I would actually look to count any of the rows that "are not equal to" or "<>" your status of "Complete":

=IF(COUNTIFS({CEM Risk_Customer Code}, [Customer Code]@row, {CEM Risk_Status}, <> "Complete") > 0, "Yes", "No")

This will also count blank Status cells though, is that ok?

Josh Caldwell

Got it! Since I also have "Completed" and "Terminated" status to exclude, this seems to work specifying each open status by adding OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch")

=IF(COUNTIFS({CEM Risk_Customer Code}, [Customer Code]@row, {CEM Risk_Status}, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch")) > 0, "Yes", "No")

Thanks again!