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.

Screenshot Months.png

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.