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.

**If no match is found, the MATCH function will return the position of the last value in the array.****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] )

- If
**LOOKUP**can’t find a value in the, it returns the position of the largest/last value in the array that is less than or equal to*lookup_range*.*lookup_range* - 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

**then, the LOOKUP function returns the position of the largest or last value in the**

*lookup_range***and returns the value in the same position in theÂ**

*lookup_range (18)*

*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

Â

## Recent Comments