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.
Answers
-
Can you take a couple of screenshots of your data sheet that you're COLLECTing from with the columns that you're referencing?
-
@Brian_Richardson sorry for the delayed response. I have attached a couple of screenshots. Not sure if its enough for you, but the formula screenshot shows the formula in the month column. The Formula 2 screenshots is where the data is coming from on the balance of the formula.
Does this help? PLMK if you need something further
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!