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.

**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.

**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).**

**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.

**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.

** 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.

- 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)`

- 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)`

**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**.

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**.

** 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.

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.

**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**.

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**.

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**

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)We just need to change the month name of

Cell G4. Hope you will get your desired solution. Regards-Alok Paul

Author at ExcelDemy