Ranking/VLookup Formula with Duplicates


I have an issue with my sheet where we rank the total number of projects closed from 1-5. Rank is in the first column, second column is a formula: =MAX(COLLECT([Total Project Closed]:[Total Project Closed], [Rank for Completion]:[Rank for Completion], [Rank for Sheds Comp]@row)) this returns the number of projects closed. And the third column is the Month and has a formula: =VLOOKUP([Rank for Sheds Comp]@row, [Rank for Completion]1:[Month of completed]34, 3, false) This looks up that ranking and returns the month.

The problem is rank 3 and 4 have returned 25 projects completed and it's produced a #NO MATCH error on rank 4 for the month.

How can I overcome this problem? Attached a screenshot to explain.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!