Formula Help

swaldon2

Hi, I have this formula that pulls from the values below (see screenshot) but the formula says #NO MATCH when no stage is started, then again once all stages are complete. Can someone help me with a formula that will give another value other than #NO MATCH. It should say "New Project" before the stages start and "Project Complete" once all stages are complete.

This is the current formula (Metrics is column 1, and value is column 2):

=INDEX(Metrics22:Metrics26, MATCH("STAGE IN PROCESS", Value22:Value26, 0))

swaldon2

I solved this by adding two more helper rows to the table above so that if the project plan is at 0% it says "New Project" instead of # NO MATCH and then if the project is at 100% it says "Project Complete" and if it there is no match, aka in between projects it says "No Stage" with adding and =iferror function into my original formula

Razetto

@swaldon2 Have you tried using a different function? See if this one works out: =IF(COUNTIF(Column2:Column2, @cell = "Complete") = COUNT(Column2:Column2), "Complete", IF(COUNTIF(Column2:Column2, @cell = "Not Started") = COUNT(Column2:Column2), "Not Started", "In Progress"))

swaldon2

@Razetto That formula works but I need where it says #NO MATCH in my screenshot above to say what stage the project is in (proposal, interconnection, ntp, etc)

Razetto

@swaldon2 If that's what you need try a different combination like =IF(MATCH("In Progress", Column2:Column2, 0) = 1, "proposal", if(match(.....))=2,"interconnection", if(match...)=3.....

swaldon2

I solved this by adding two more helper rows to the table above so that if the project plan is at 0% it says "New Project" instead of # NO MATCH and then if the project is at 100% it says "Project Complete" and if it there is no match, aka in between projects it says "No Stage" with adding and =iferror function into my original formula