Duplicate rank what formula would return the most recent week?
I have a formula to return the date of the 5 highest ranking weeks. Two weeks are both ranking as the 5th week as they both have the same result. Currently it is returning the oldest of the two dates. Is there a way to get the most recent week instead?
Best Answer

Hi @B Young
I agree with Paul! You could use a MAX function, however I would put this in an MAX(COLLECT formula instead of VLOOKUP.
Try something like this:
=MAX(COLLECT([Week Column]:[Week Column], [Rank of boxes ticked]:[Rank of boxes ticked], Rank@row)
I'm not sure what your Week Column is called, so you may need to replace that with the column that has your dates. Let us know if that worked for you!
Cheers,
Genevieve
Answers


Thank you for your suggestion @Paul Newcome. Could you help me further on this? I have included a screenshot of the data.
Week commencing column has formula: =VLOOKUP(Rank@row, [Rank of boxes ticked]:[TOTAL BOXES TICKED], 2, false)
Rank column is just numerical 15
No Ticked has formula: =VLOOKUP(Rank@row, [Rank of boxes ticked]:[TOTAL BOXES TICKED], 3, false)
How does the max function fit into these formulas? Or should the formula be recreated?

Hi @B Young
I agree with Paul! You could use a MAX function, however I would put this in an MAX(COLLECT formula instead of VLOOKUP.
Try something like this:
=MAX(COLLECT([Week Column]:[Week Column], [Rank of boxes ticked]:[Rank of boxes ticked], Rank@row)
I'm not sure what your Week Column is called, so you may need to replace that with the column that has your dates. Let us know if that worked for you!
Cheers,
Genevieve

Thank you @Genevieve P. this has helped solve the problem. Appreciate your help.
Help Article Resources
Categories
Check out the Formula Handbook template!