Ranking/VLookup Formula with Duplicates

B Young

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.

Screenshot Months.png

Brian_Richardson

Can you take a couple of screenshots of your data sheet that you're COLLECTing from with the columns that you're referencing?

B Young

@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

Formulas 2.png

Formulas.png