Forum Discussion
Sort data if rank is found with RANK formula
Good morning,
I have a project I am completing and the final step is to get the date shown in the sample file to sort based on the numbered rank under the "RANK" column that is populated using the =RANK formula. When I sort it, it messes up the formulas and this is a dynamic return of data that will change week to week. I need it to move to where the red arrow points based on the rank of the highlighted column that uses the RANK formula based on the percentage in W.
Thank you for any assistance!
Have you tried using XLOOKUP (or INDEX/MATCH) to look up the Vendor and Percentage based on the rank? E.g.
=XLOOKUP(F18,$X$2:$X$12,$V$2:$V$12)
=XLOOKUP(F18,$X$2:$X$12,$W$2:$V$12)
or
=INDEX($V$2:$V$12,MATCH(F18,$X$2;$X$18,0))
=INDEX($W$2:$W$12,MATCH(F18,$X$2;$X$18,0))
2 Replies
Have you tried using XLOOKUP (or INDEX/MATCH) to look up the Vendor and Percentage based on the rank? E.g.
=XLOOKUP(F18,$X$2:$X$12,$V$2:$V$12)
=XLOOKUP(F18,$X$2:$X$12,$W$2:$V$12)
or
=INDEX($V$2:$V$12,MATCH(F18,$X$2;$X$18,0))
=INDEX($W$2:$W$12,MATCH(F18,$X$2;$X$18,0))
- XLOOKUP worked perfectly, thank you!!
