FIND THE LAST OR Nth VALUE IN EXCEL (SORTED LIST)

Finding the Nth or the Last value in a sorted or unsorted list can pose a challenge if you do not understand which functions to use.

This article will show you different ways to carry out this sort of find and retrieve an item in a sorted list.

How to handle the unsorted list will be tackled in the next article.

To show the different methods I have created a list of customers and their Purchase Orders (P.O). You can download it to follow the examples.

FINDING Nth VALUE

For example, how do you retrieve the 2nd P.O received from customer Carl Ludwig?

1. Using INDEX, SMALL & IF ►►►ARRAY FORMULA

{=INDEX(Customers[PO No.],SMALL(IF(Customers[Customer Name]="Carl Ludwig",ROW(Customers[Order Date])-ROW(Customers[[#Headers],[Order Date]])),2)))}=116014

How It Works

=INDEX(Customers[PO No.] ► ► ►Is used to fetch the P.O numbers 

=SMALL(► ► ► Supplies the Row number to the INDEX function

=IF( ► ► ► Supplies the SMALL Function with an array of Row Numbers

Customers =” Carl Ludwig” ► ► defines the criteria to use when fetching the row number

ROW(Customers[Order Date])–ROW(Customers[[#Headers],[Order Date]]) ► ► Returns the standardized Row numbers.  NB: You must Deduct the Header Row number to standardize the row numbers

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}

Below is what the IF function returns based on the criteria,

SMALL({1;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;11;FALSE;FALSE;14;15;FALSE;FALSE;FALSE;FALSE;20;FALSE},2)

Then SMALL function selects the nth one from this array. And since our n =2 then SMALL returns row 7

This is the row number that the INDEX uses to return the P.O number.

Therefore, =INDEX(Customers[PO No.],7) = 116014

2.Using AGGREGATE  Function

=AGGREGATE(15,6,(Customers[PO No.]/(Customers[Customer Name]="Carl Ludwig")),2)=116014

 How It Works

The trick in using the AGGREGATE function is to define the array for the SMALL function

So select 15 ► SMALL Function  and option 6 ►Ignore error values

Customers[PO No.] returns all P.O numbers

While (Customers[Customer Name]=”Carl Ludwig”)  Returns a Boolean array, TRUE where criteri0n is met otherwise FALSE. This boolean array is converted to its numeric equivalent (TRUE = 1 & FALSE =0) during division.

Therefore, (Customers[PO No.]/(Customers[Customer Name]=“Carl Ludwig”)) results to P.O number where the divisor is 1 and Error where the divisor is 0

{105686;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;116014;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;126822;#DIV/0!;#DIV/0!;136418;121268;#DIV/0!;#DIV/0!;141289;#DIV/0!;#DIV/0!;#DIV/0!}

Since we had selected option 6 ► ignore Zero Values the final array is {105686;116014;126822;136418;121268;141289}

AGGREGATE Function selects the 2nd smallest value

FINDING the LAST OCCURRENCE IN A LIST

Using the same example above, find the last P.O received from customer Carl Ludwig

1.Using INDEX & MAX 

=INDEX(Customers[PO No.],MAX((Customers[Customer Name]="Carl Ludwig")*(ROW(Customers[Order Date])-ROW(Customers[[#Headers],[Order Date]]))))=141289

The trick in using this method is to find the Last row (one with maximum row number) that contains Carl Ludwig’s data in the list.

The MAX function is supplied by ONLY an array of Row numbers that meet the criteria (Customers[Customer Name] =”Carl Ludwig”)

=MAX({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21})

Which simplifies to

=MAX({1;0;0;0;0;6;0;0;0;0;11;0;0;14;15;0;0;18;0;0;0})= 18

2.Using MAXIFS

=MAXIFS(Customers[PO No.],Customers[Customer Name],"Carl Ludwig")=141289

If you have office 2019 or office365 subscription, MAXIFS function is the simplest and cleanest function to use

3.  Using INDEX & MATCH

=INDEX(Customers[PO No.],MATCH(2,1/(Customers[Customer Name]="Carl Ludwig")))= 141289

The trick in the method is in understanding the 2 traits of MATCH function ie.

1. If no match is found, the MATCH function will return the position of the last value in the array.
2. MATCH function does not return the position of an error or blank value

Since, MATCH(2,1/(Customers[Customer Name]=”Carl Ludwig”)) evaluates to

MATCH(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!})

And value 2 cannot be found in the array, MATCH returns the position of the last Value (1) i.e 18

Therefore, =INDEX(Customers[PO No.], 18) = 141289

4. Using LOOKUP 

=LOOKUP(2,1/(Customers[Customer Name]="Carl Ludwig")),Customers[PO No.])

The trick here is in understanding the LOOKUP function which has a syntax  LOOKUP( value, lookup_range, [result_range] )

1. If LOOKUP can’t find a value in the lookup_range, it returns the position of the largest/last value in the array that is less than or equal to lookup_range.
2. The LOOKUP function then uses this position to returns the value from the same position in the  result_range.

Since, LOOKUP(2,1/(Customers=G9),Customers[PO No.]) evaluates into below

=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!},Customers[PO No.])

And the value 2  cannot be found in the lookup_range then, the LOOKUP function returns the position of the largest or last value in the lookup_range (18) and returns the value in the same position in the result_range (Customers[PO No.]).

Download Worksheet

RECOMMENDED LINKS:

• get-digital-help.com/2014/02/07/find-last-matching-value-in-an-unsorted-list/
• find-the-last-occurrence-of-an-item-in-a-list-in-excel/
• https://blog.contextures.com/archives/2014/02/04/find-last-item-in-group-with-index-match/#comment-282746