Both MIN and MAX in Excel will return the Minimum or Maximum value from a list.  But what if you want to return a value that is the Nth largest in Excel? Or even the Nth smallest . How would you go about this type of ranking?

You could use one of Excels Rank functions and sort by rank, but this is not really the best option.

OFFSET to find the Nth Largest in Excel 

You could also use the OFFSET function.  To do this you would first need to sort your data.  If you are looking for the Nth Largest, the sorting would be from the highest value to the lowest value.

=OFFSET(A1,D2,0)

Where:

• A1 is the starting position.  I have selected my column header as the starting position.  
• D2 is where I have entered the nth largest number I wish to find.  In this example it is 5

What the OFFSET function will do is move by the number of rows and columns you tell it to move and then return the value from that cell.

By selecting D2, which contains the value of 5 in this case, we are telling offset to move down 5 rows from the starting point, which is the column header.

At the end of the function we see 0.  This represented the number of columns we want to move.  By the way, the value returned using the offset function is 143, the 5th largest number in the list.

If you want to learn more about the OFFSET function have a read of OFFSET Function to make Excels SUM function Dynamic.

Alternative to OFFSET – SMALL and LARGE

The drawback of using OFFSET is the requirement to have your data sorted.  Therefore it can be prone to human error.   Excel has a nice little function, well two functions really that will allow you handle this.  So check out the video below for this time saving nifty Excel trick.

And Hay, don’t forget to give this video the thumbs up!