RANK and XLOOKUP – how can I use both of these my worksheets?

RANK and XLOOKUP in Excel: Excel icon​This week’s hint and tip is about the functions RANK and XLOOKUP in Excel and how you can use them in your worksheets. In this hint and tip we are showing you how you can use Excel for sporting and other competitions. We are going to go through it now below.

 

Using RANK and XLOOKUP together in a spreadsheet

Some of you may know that John, our main trainer, takes part in triathlons and is a member of a local club. So this Excel workbook shows how we store the times for each of the 3 disciplines (Swim, Cycle, Run) and then allocate medals according to position.

We use the RANK and XLOOKUP function to achieve this and format cells as times in hours minutes and seconds as appropriate. As well as this named ranges are also used for the times per discipline.

 

Pointers to look out for in the video

Key features used are:

    • Named ranges are an alternative to using $ signs. They are created in the Name Box (above column A) but edited via Name Manager on the Formulas Tab
    • The RANK formula has 3 arguments: cell reference, named range and numeric value of 1 (ascending)
    • XLOOKUP is used to get the medal allocated and has the advantage over VLOOKUP of having the matched value not necessarily in column1 (here is the 2nd column we are matching to and picking up the medal from the 1st column)

 

Please visit our YouTube Channel directed from our website or via this link for more videos.

 

The video below shows you how the two functions are put together and used in a spreadsheet. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

Click on button to the right to download the example spreadsheet shown in the video to try it out yourself!

We hope you have enjoyed this hint and tip on RANK and XLOOKUP in Excel. Why not take a look at our previous one on part 2 of our Excel shortcuts​?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy