INDEX, MATCH and MAX with Multiple Criteria in Excel

Combining INDEX, MATCH, and MAX functions is one of the most comprehensive formulas which will let you extract maximum or largest values under multiple criteria. The screenshot represents our dataset that will be used in this article to show the applications of numerous functions. The table shows 4 columns with the names of computer brands, device types, their model names, and prices.

index-match-max-dataset-overview


Introduction to INDEX, MATCH, and MAX Functions

1. INDEX

  • Formula Syntax:

=INDEX(array, row_num, [column_num])

or,

=INDEX(reference, row_num, [column_num], [area_num])

  • Activity:

Returns a value of reference of the cell at the intersection of the particular row and column, in a given range.

  • Example:

Based on our dataset mentioned earlier, we’ll use the INDEX function to find the value of the cell at the intersection of the 3rd row and 4th column from the array of B3:E15. So, our formula in Cell H9 will be:

 =INDEX(B3:E15,3,4)

After pressing Enter, you’ll get the return value as $880.00 which lies at the intersection of the 3rd row and 4th column in the selected array.

index introduction


2. MATCH

  • Formula Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

  • Activity:

Returns the relative position of an item in an array that matches a specified value in a specified order.

  • Example:

By inserting the MATCH function for our dataset, we’ll find out which row contains model CF2235. So, the related formula in Cell H9 will be:

=MATCH(H8,D3:D15,0)

Now press Enter and you’ll be shown ‘7’ as the result. It means the selected model name is lying at the 7th row in the column of Model (Column D).

match introduction


3. MAX

  • Formula Syntax:

=MAX(number1, [number2],…)

  • Activity:

Returns the largest value in a set of values, ignores logical values and text strings.

  • Example:

We’ll use the MAX function to find the maximum or highest price from Column E. So we have to type in Cell G9:

=MAX(E3:E15)

After pressing Enter, you’ll be shown the highest or maximum price instantly from the column with the Price header.

max introduction


INDEX, MATCH and MAX with Multiple Criteria in Excel: 2 Suitable Ways

Method 1 – Using INDEX, MATCH and MAX Functions Together to Get the Maximum Price

Steps:

  • Select the output Cell H10 and insert the following:
=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 4)
  • Press Enter and you’ll get the highest price and can use it as the basis for the item’s information.

index match max to find maximum value with multiple criteria

How Does This Formula Work?

MAX function here pulls out the largest or maximum value from the range of Cells E5:E17.

MATCH function finds out the row position of that maximum value.

➤ Within the INDEX function, B5:E17 is the entire array where our data extraction functions are being applied and the other arguments are showing row number and column number.

➤ Here, ‘4’ has been chosen as a column number since the price list is present in the 4th column of the selected array.

INDEX function now extracts the data from Column E based on the row and column criteria.

  • Now, we’ll find out the brand name for the highest price. So in our output Cell H11, the related formula will be:
=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 1)

Here, 1 is the column number for the INDEX function as all brand names are present in the 1st column of the selected array.

index match max to find maximum value with multiple criteria

  • In Cell H12, we’ll determine the type of device for the maximum price. The column number will be 2. So, the embedded formula will be in Cell H12:
=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 2)

index match max to find maximum value with multiple criteria

  • Finally, we’ll determine which model has the maximum price. So, in Cell H13, the formula will be:
=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 3)

index match max to find maximum value with multiple criteria


Method 2 – Using INDEX, MATCH, and MAX Functions Together to Find the Latest Date

In our modified dataset below, the Date of Manufacture column has been added to assign dates. We’ll find out the latest date among all.

Steps:

  • Select Cell H10 and input:
=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 4)
  • Press Enter.

index match max to find latest date with multiple criteria

As the dates are present in the 4th column of the selected array, we’ve assigned the column number of the INDEX function as 4. If we want to extract the model name, device type, and brand name for that particular date of manufacture, we have to simply change the column number for the INDEX function based on the criteria position in the array.


Alternative Methods to INDEX, MATCH, and MAX Functions

Method 3 – Combining INDEX, MATCH, and AGGREGATE Functions to Determine Maximum Value with Multiple Criteria

Let’s learn more about the AGGREGATE function first before applying the function in this section.

  • Formula Syntax:

=AGGREGATE(function_num, options, array, [k])

or,

=AGGREGATE(function_num, options, ref1, ref2, [ref3],…)

  • Arguments:

function_num- A list of 19 functions with serial numbers will appear. You have to select the function required with the serial number.

options- An option to choose that will ignore the error or numerical data.

array- Selected array or range of cells where the formula will work

[k]- serial number or position in the array based on the return values

  • Activity:

Returns an aggregate in a list or database.

We’re going to determine the highest price of the notebook of the Compufy brand.

Steps:

  • Select the output Cell H12 and insert:
=AGGREGATE(14, 6, E5:INDEX(E:E, MATCH(1E+99,E:E))/((B5:INDEX(B:B, MATCH(1E+99,E:E ))=H9)*(C5:INDEX(C:C, MATCH(1E+99,E:E ))=H10)),1)
  • Press Enter.

index match max alternative to find maximum value with multiple criteria

How Does This Formula Work?

➤ Inside the array, 14 is the Function Number that is assigned to the LARGE function.

➤ 6 has been chosen as Option Number which ignores the Error Values.

➤ In the 3rd argument, a complex array has been established. The dividend or numerator part returns an array with all prices from the list and it looks like-

{1070;970;880;1090;990;730;870;810;910;1020;890;900;1000}

➤ The divisor or denominator has two parts and both work with logical functions. The 1st part looks for the brand name Compufy in Column B while the 2nd part looks for a notebook device in Column C. Then the converted (TRUE=1, FALSE=0) and numerical logic values are multiplied alongside these two parts. So, the resultant array returns as-

{0;0;0;0;1;0;1;0;0;1;0;0;0}

➤ Now, all the prices found from the dividend will be divided with these logical values found from the divisor and it’ll return as-

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;990;#DIV/0!;870;#DIV/0!;#DIV/0!;1020;#DIV/0!;#DIV/0!;#DIV/0!}

➤ Finally, the AGGREGATE function ignores all the Error Values and extracts the largest value from the array.


Method 4 – Fusing LARGE Function with INDEX-MATCH to Find the Highest or Largest Three

In our new and modified dataset below, prices of different models of 4 computer brands are present.

index match large to find maximum value with multiple criteria

We’ll find out the highest three prices of a specific brand, let’s say OMICSIRIX.

Steps:

  • In Cell E17, our formula for the mentioned criteria will be:
=LARGE(INDEX($C$4:$G$14, MATCH($E$16, $B$4:$B$14, 0), 0), ROWS($D$17:$D17))
  • Press Enter.
  • To get the 2nd and 3rd highest prices, use the Fill Handle option to fill down the Cells E18 and E19.

Here, in Cell E16, if you change the brand name, you’ll find the highest 3 prices of that brand.

index match large to find maximum value with multiple criteria


Method 5 – Incorporating MAX and IF Functions to Find the Maximum Value with Multiple Criteria

Based on our dataset, we’ll find out the maximum price of Compufy notebook with the combination of MAX and IF functions.

Steps:

  • The formula in the output Cell H11 will be:
=MAX(IF(B5:B17=H9,IF(C5:C17=H10,E5:E17)))
  • Press Enter.

index match max alternative max if to find maximum value with multiple criteria

Inside this formula, IF functions are extracting the data based on the criteria. Then the MAX function will determine the largest one from them.


Method 6 – Using MAXIFS Function to Determine Maximum or Largest Value with Multiple Criteria

Let’s find out the maximum price of the Compufy notebook once again.

Steps:

  • In Cell H12, our formula will be:
=MAXIFS(E5:E17,B5:B17,H9,C5:C17,H10)
  • Press Enter.

index match max alternative maxifs to find maximum value with multiple criteria

The MAXIFS function takes the range of data as 1st argument, 2nd argument here is the criteria range, and 3rd argument is the criteria, and finally returns the value from the 1st argument based on the inputted criteria. To add multiple criteria, we have to use Comma(,) between two criteria inside the function.


Download Practice Workbook

You can download our Excel Workbook that we’ve used to prepare this article. You’ll be able to modify input data and see how the table responds.


<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. sir i want to make a format in which product name & monthly min & max sale price show.
    which formula is applicable please guide me

    • Hello, JITENDRA!
      Hope you are doing well. You have a query to get the product name and min & max sell price monthly basis. We have to use the following formulas for that.
      For Max values:
      =MAXIFS($E$5:$E$17,$D$5:$D$17,$G$4)

      For Min values:
      =MINIFS($E$5:$E$17,$D$5:$D$17,$G$4)

      For Product Name:
      =INDEX($B$5:$E$17,MATCH($J$6,$E$5:$E$17,0),1)

      INDEX- MATCH Function
      We just need to change the month name of Cell G4. Hope you will get your desired solution. Regards
      -Alok Paul
      Author at ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo