FIND NTH IN EXCEL

FIND THE LAST OR Nth OCCURENCE 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 in a sorted list.

How to handle 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?

Finding nth or last in a sorted list

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

{=INDEX(E2:E20,SMALL(IF(Customers="Carl Ludwig",ROW(Order_Quantity)-ROW(D1)),2))}= 114143

How It Works

=INDEX(E2:E20 ► ► ►Is used to fetch the P.O numbers after being given the row number by the SMALL Function.

{105686;121018;113075;116058;116418;110033;112798;113809;113756;108836;109427;116014;114633;115342;114047;120918;114143;116293;106822}

=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

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

ROW(Order_Quantity)-ROW(D1) ► ► 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}

So, after a complete evaluation, the IF function results to an array of row numbers that meets the criteria,

{FALSE;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;FALSE;FALSE;FALSE;FALSE;FALSE;17;FALSE;FALSE}

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

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

Therefore, =INDEX(E2:E20, 11) = 114143

2.Using AGGREGATE  Function

=AGGREGATE(15,6,(E2:E20/(Customers="Carl Ludwig" )),2) =114143

 How It Works

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

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

E2:E20 returns all P.O numbers

{105686;121018;113075;116058;116418;110033;112798;113809;113756;108836;109427;116014;114633;115342;114047;120918;114143;116293;106822}

While Customers=”Carl Ludwig”  Returns a Boolean array, TRUE where criteria is met otherwise FALSE

NB: The boolean array is converted to its numeric equivalent where TRUE = 1 & FALSE =0 during division

So, (E2:E20/(Customers= “Carl Ludwig” ) results to P.O number where the divisor is 1 and Error where divisor is 0

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;112798;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;114143;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;116418;#DIV/0!;#DIV/0!}

Since we had selected option 6 ► ignore Zero Values the final array is {112798;114143;116418}

AGGREGATE Function selects the 2nd smallest value

3. Using LOOKUP & COUNTIFS ►► NON-ARRAY FORMULA

=LOOKUP(2,1+COUNTIFS(Customers,"Carl Ludwig",A2:A20,"<"&A2:A20),F2:F20)

This last formula was suggested by Peter Batholomew in a Linkedin Forum.

How It works

See Peter’s explanation on Linkedin

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   ►► ARRAY FORMULA

{=INDEX(E2:E20,MAX((Customers=G3)*ROW(E2:E20)-ROW(E1)))} =116418

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

The MAX function is supplied by ONLY an array of Row numbers that meets the criteria (customer =”Carl Ludwig”)

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

Which simplifies to

=MAX({0;0;0;0;0;6;0;0;0;0;11;0;0;0;0;0;17;0;0})= 17

2.Using INDEX & SUMPRODUCT ► ►NON- ARRAY FORMULA

=INDEX(E2:E20,SUMPRODUCT(MAX((Customers=G3)*ROW(E2:E20)-ROW(E1)))) =116418

If you do not like array formulas, you can wrap MAX function inside SUMPRODUCT which will handle the array for you.

3.  Using INDEX & MATCH  ►► ARRAY FORMULA

{=INDEX(E2:E20,MATCH(2,1/(Customers=G3)))} = 116418

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

  1. If no match is found, 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=G3)) 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 17

Therefore, =INDEX(E2:E20, 17) = 116418

4. Using LOOKUP ► ►NON-ARRAY FORMULA

=LOOKUP(2,1/(Customers=G9),E2:E20)

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),E2:E20) 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!},E2:E20)

And the value 2  cannot be found in the lookup_range then, LOOKUP function returns the position of the largest or last value in the lookup_range (17) and returns the value in the same position in the result_range (E2:E20).

Download Worksheet

RECOMMENDED LINKS:

 

Leave a Comment:

Your email address will not be published. Required fields are marked *