In the previous article, we looked at 6 ways to find the last or Nth occurrence in a Sorted list in excel.

Unsorted list poses a challenge since you need to find the occurrence using multiple criteria.

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.

The list is neither sorted datewise nor does its PO numbers serialized.

FINDING Nth VALUE

From the below example, retrieve the 2nd P.O received from customer Carl Ludwig?

The above challenge cannot be solved by simply checking the row numbers, as shown in the previous article,  It requires we use the Order Date & Customer name criteria.

1. Using INDEX, MATCH, SMALL & IF

=INDEX(Customers[PO No.],MATCH(1,(Customers[Customer Name]="Carl Ludwig")*(Customers[Order Date]=SMALL(IF(Customers[Customer Name]="Carl Ludwig",Customers[Order Date]),2)),0))

How It Works

► ►=INDEX(Customers[PO No.]

Returns the P.O number given the location (row number) by the MATCH function

► ►MATCH(1,(Customers[Customer Name]=”Carl Ludwig”)*(Customers[Order Date]=SMALL(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),2)),0)

MATCH selects the unique row number where the customer is Carl Ludwig AND the Order_Date is the 2nd smallest.

Since this is multiple criteria, MATCH function searches through the rows in the data and return the row number where all of our criteria are TRUE

The first criterion to check is if the customer is  Carl Ludwig

►Customers[Customer Name]=”Carl Ludwig”

The second criterion is to check if the 2nd Smallest Order_Date for the selected customer

►Customers[Order Date]=SMALL(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),2)

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

This is the tricky part since we have to use the SMALL function to select the 2nd Smallest date.

NB: SMALL function is able to sort and select dates since Excel stores date as Serial numbers.

► SMALL(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),2)

The SMALL function has the syntax SMALL(array,k) i.e returns the K’th smallest value in an array. In our example, we need it to return the 2nd smallest Order_Date for customer Carl Ludwig

►IF(Customers[Customer Name]=”Carl Ludwig”, Customers[Order Date]) ensures it returns ONLY an array of Order_Dates for customer Carl Ludwig 

When you multiply the 2 boolean arrays from the above criteria tests, the result is as below

MATCH(1,{0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0},0) = 9

Therefore,

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

2. Using SUMPRODUCT, SMALL & IF

=SUMPRODUCT(Customers[PO No.]*(Customers[Customer Name]=”Carl Ludwig”)*(Customers[Order Date]=SMALL(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),2)))

How It Works

►Customers[PO No.] returns an array of all P.O numbers

►(Customers[Customer Name]=”Carl Ludwig”) returns a boolean array, TRUE being the location where the criterion is met

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

►(Customers[Order Date]=SMALL(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),2)) checks the  2nd smallest Order_Date for selected customer. It returns the boolean array as shown below

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

When SUMPRODUCT multiplies the 3 arrays, you get an array of 0 where Not all criteria were met or 1 where all criteria were met

=SUMPRODUCT({0;0;0;0;0;0;0;0;116014;0;0;0;0;0;0;0;0;0;0;0;0})=116014

FINDING the LAST OCCURRENCE USING MULTIPLE CRITERIA

Using the same example above, find the last P.O received from customer Carl Ludwig. Since the list is unsorted datewise, we have to check the recent date and return the corresponding PO number

1.Using SUMPRODUCT, LARGE & IF

=SUMPRODUCT(Customers[PO No.]*(Customers[Customer Name]="Carl Ludwig")*(Customers[Order Date]=LARGE(IF(Customers[Customer Name]="Carl Ludwig",Customers[Order Date]),1)))

How It Works

►Customers[PO No.] returns an array of all P.O numbers

►(Customers[Customer Name]=”Carl Ludwig”) returns a boolean array, TRUE being the location where the criterion is met

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

►(Customers[Order Date]=LARGE(IF(Customers[Customer Name]=”Carl Ludwig”,Customers[Order Date]),1)) checks the  Largest Order_Date for selected customer. It returns the boolean array as shown below

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

When SUMPRODUCT multiplies the 3 arrays, you get an array of 0 where Not all criteria were met or 1 where all criteria were met

=SUMPRODUCT({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;141289})

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. INDEX, MATCH, MAX

=INDEX(Customers[PO No.],MATCH(MAX((Customers[Customer Name]="Carl Ludwig")*Customers[Order Date]),(Customers[Customer Name]="Carl Ludwig")*Customers[Order Date]))

►(Customers[Customer Name]=”Carl Ludwig”)*Customers[Order Date]) returns an array of Order_dates for Customer Carl Ludwig

MAX({0;0;0;41125;0;0;0;0;43747;0;0;43899;0;0;0;43822;0;0;43899;0;44031})

MAX function picks the largest, which MATCH function returns its location in the array of Order_Dates

INDEX Function then returns the PO based on the row location.

DOWNLOAD WORKSHEET

RELATED LINKS:

FIND THE LAST OR NTH OCCURRENCE IN EXCEL (SORTED LIST)

RECOMMENDED LINKS

• https://www.deskbright.com/excel/index-match-multiple-criteria/
• https://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list
• MATCH function using multiple criteria