FIND N using multiple criteria

FIND THE LAST OR Nth OCCURENCE IN EXCEL USING MULTIPLE CRITERIA

In the previous article, we looked at 7 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 below example, retrieve the 2nd P.O received from customer Carl Ludwig?

find nth in excel

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

{=INDEX(E2:E20,MATCH(1,(Customers="Carl Ludwig")*(Order_Date=SMALL(IF(Customers="Carl Ludwig",Order_Date),2)),0))}

How It Works

Firstly, the { } around the formula indicates that this is an array formula–Don’t type these braces as part of the formula. Instead, press Ctrl+Shift+Enter key when entering the formula or if you are using a Mac press CMD+Enter.

► ►=INDEX(E2:E20

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

► ►MATCH(1,(Customers=“Carl Ludwig”)*(Order_Date=SMALL(IF(Customers=“Carl Ludwig”,Order_Date),2))

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

Since this 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=“Carl Ludwig”)

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

The second criterion is to check if the 2nd Smallest Order_Date for the selected customer ►(Order_Date=SMALL(IF(Customers=“Carl Ludwig”,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=“Carl Ludwig”,Order_Date),2)

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=“Carl Ludwig”,Order_Date) ensures it returns ONLY an array of Order_Dates for customer Carl Ludwig 

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

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

Therefore,

=INDEX(E2:E20,17) = 127729

2. Using SUM, SMALL & IF  ►► ARRAY FORMULA

{=SUM((E2:E20)*(Customers="Carl Ludwig")*(Order_Date=SMALL(IF(Customers="Carl Ludwig",Order_Date),2)))}

How It Works

► (E2:E20) returns an array of all P.O numbers

{105686;121018;113075;105132;97189;89246;113798;113809;113756;108836;109427;110018;110609;111200;114047;120918;127789;134660;141531}

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

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

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

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

When you multiply the 3 arrays, you get an array of 0 where either all criteria or 1 criterion was not met and P.O number value where all criteria were met

=SUM({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;127789;0;0})=127789

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 INDEX, MATCH, MAX & IF ►► ARRAY FORMULA

{=INDEX(E2:E20,MATCH(MAX(IF(Customers="Carl Ludwig",Order_Date)),Order_Date,0))}

How it Works

► ►=INDEX(E2:E20

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

►►MATCH(MAX(IF(Customers=G10,Order_Date)),Order_Date,0)

MATCH function returns a row number  for which contains the customer’s Most recent Order_date as selected by MAX function

►MAX(IF(Customers=”Carl Ludwig”,Order_Date)) returns the most recent Order_date IF the customer is Carl Ludwig.

MAX({FALSE;FALSE;FALSE;FALSE;42308;FALSE;41752;FALSE;42130;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;41813;FALSE;FALSE})

MATCH function checks and returns the location of this recent Order_date from the array of Order_dates

MATCH(42308,{41671;42019;41754;41934;42308;41743;41752;41789;42130;41699;41703;41909;41818;41859;41792;41979;41813;41939;41682},0) =5

INDEX uses the location to retrieve the PO number

=INDEX(E2:E20,5) = 97189

2. Using SUM, MAX, IF ►► ARRAY FORMULA

{=SUM(E2:E20*(Order_Date=MAX(IF(Customers="Carl Ludwig",Order_Date)))*(Customers="Carl Ludwig"))}

How It Works

► (E2:E20) returns an array of all P.O numbers

{105686;121018;113075;105132;97189;89246;113798;113809;113756;108836;109427;110018;110609;111200;114047;120918;127789;134660;141531}

►(Order_Date=MAX(IF(Customers=”Carl Ludwig”,Order_Date))) returns a boolean array, TRUE being location of the recent Order_Date

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

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

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

When you multiply the 3 arrays, you get an array of 0 where either all criteria or 1 criterion was not met and P.O number value where all criteria were met

=SUM({0;0;0;0;97189;0;0;0;0;0;0;0;0;0;0;0;0;0;0}) =97189

3. INDEX, MATCH, MAX ► ►NON-ARRAY FORMULA

=INDEX(E2:E20,MATCH(MAX(INDEX((Customers="Carl Ludwig")*Order_Date,0)),Order_Date,0))

This is a non-array method, so no need for pressing Ctrl+Shift+Enter when entering the function

The other difference from Method 1 above is that it uses the INDEX function to handle the array of filtered dates unlike IF function which is poor in handling arrays.

►INDEX((Customers=”Carl Ludwig”)*Order_Date,0) returns an array of 0 for dates where the customer criterion is not met and Order_dates for Customer Carl Ludwig

MAX({0;0;0;0;42308;0;41752;0;42130;0;0;0;0;0;0;0;41813;0;0})

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.

4. Using LOOKUP, SUMPRODUCT, MAX ►► NON-ARRAY FORMULA

=LOOKUP(2,1/(Order_Date=SUMPRODUCT(MAX((Customers="Carl Ludwig")*Order_Date))),E2:E20)

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

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

Since, Lookup_range [ 1/(Order_Date=SUMPRODUCT(MAX((Customers=G10)*Order_Date)))] evaluates into below array

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

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 (5) and returns the value in the same position in the result_range (E2:E20)

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

Leave a Comment:

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