Duplicate rank what formula would return the most recent week?

B Young

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?

Genevieve P.

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

Paul Newcome

You would use a MAX function.

B Young

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 1-5

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?

Ranking.png

Genevieve P.

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

B Young

Thank you @Genevieve P. this has helped solve the problem. Appreciate your help.