In Microsoft Excel, the combination of INDEX, MATCH & MAX functions is one of the most comprehensive formulas which will let you extract maximum or largest values under multiple criteria. In this article, you’ll get to know how we can use INDEX, MATCH & MAX functions together to find out the largest values from a table or an array.

The above screenshot represents our dataset that will be used in this article to show the applications of numerous functions with proper illustrations. The table shows 4 columns with the names of computer brands, device types, their model names & prices.

**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 & find new outputs instantly.

**Introduction to INDEX, MATCH & 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 & 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 & 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 **& you’ll be shown **‘7’** as the resultant value. It means the selected model name is lying at the 7th row in the column of Model **(Column D).**

**Read More:** **Index Match Multiple Criteria in Rows and Columns in Excel**

**3. MAX**

**Formula Syntax:**

**=MAX(number1, [number2],…)**

**Activity:**

*Returns the largest value in a set of values, ignores logical values & 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.

**2 Suitable Ways to Use INDEX, MATCH and MAX Functions Together with Multiple Criteria**

Now we’ll see the applications of **INDEX, MAX & MATCH** functions together to find maximum or largest values under multiple criteria.

**1. Using INDEX, MATCH & MAX Functions Together to Get the Maximum Price**

From our dataset table, we’ll find out first which price is the highest among all in **Column E**.

**📌**** Steps:**

➤ Select output **Cell H10** & type:

`=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 4)`

➤ Press **Enter **& you’ll get the highest price at once.

**🔎 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 & the other arguments are showing row number & 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 & 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 find out the type of device for the maximum price & here, 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 of Clocktech Desktop is showing 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)`

**Read More:** **How to Match Multiple Criteria from Different Arrays in Excel**

**2. Using INDEX, MATCH & MAX Functions Together to Find the Latest Date**

By using a similar formula, we can also find out the earliest date from a range of cells containing dates. 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** & type:

`=INDEX($B$5:$E$17, MATCH(MAX($E$5:$E$17), $E$5:$E$17,0), 4)`

➤ Press **Enter** & now you’ll find the latest date among all instantly.

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. Now if we want to extract the model name, device type & brand name for that particular date of manufacture, then we have to simply change the column number for the INDEX function based on the criteria position in the array.

**Read More:** **Excel Index Match single/multiple criteria with single/multiple results**

**Similar Readings**

**Excel INDEX MATCH If Cell Contains Text****INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)****How to Use INDEX and Match for Partial Match (2 Ways)****Sum with INDEX-MATCH Functions under Multiple Criteria in Excel****Index Match Sum Multiple Rows in Excel (3 Ways)**

**Alternative Methods to INDEX, MATCH & MAX Functions**

**1. Combining INDEX, MATCH & AGGREGATE Functions to Determine Maximum Value with Multiple Criteria**

Let’s get introduced to 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.**

Now let’s come to our dataset again. We’re going to determine the highest price of the notebook of Compufy brand.

**📌**** Steps:**

➤ Select the output **Cell H12** & type:

`=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** & you’ll get the maximum price of Compufy notebook at once.

**🔎 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 & it looks like-

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

➤ The divisor or denominator has two parts & both work with logical functions. The 1st part looks for the brand name Compufy in **Column B** & 2nd part looks for a notebook device in **Column C**. Then the converted** (TRUE=1, FALSE=0)** & 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 & 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** & extracts the largest value from the array.

**Read More:** **Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)**

**2. Fusing LARGE Function with INDEX-MATCH to Find the Highest or Largest Three**

In this section, we’ll apply the **LARGE** function which extracts the largest value from a range of cells or an array based on the defined position or serial of the largest value. In our new & 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 it be 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** & you’ll see the 1st highest price of the OMICSIRIX brand.

➤ To get the 2nd & 3rd highest prices, use the **Fill Handle** option to fill down the **Cells E18 & E19**.

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

**3. Incorporating MAX and IF Functions to Find the Maximum Value with Multiple Criteria**

Now let’s go back to our initial dataset once again. If you want to avoid** INDEX-MATCH** **functions** then you can apply **MAX & IF** functions together & get a similar result. Based on our dataset below, we shall find out the maximum price of Compufy notebook with the combination of **MAX & 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** & you’ll find the result right away.

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

**Read More: IF with INDEX-MATCH in Excel (3 Suitable Approaches)**

**4. Using MAXIFS Function to Determine Maximum or Largest Value with Multiple Criteria**

In our final alternative method, we’ll apply **MAXIFS** functions. By using this function, you don’t have to input the **IF** function anymore & all the criteria will be analyzed by the **MAXIFS** function. So, we want to 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 & you’ll be displayed the maximum price of the Compufy notebook instantly.

The **MAXIFS** function takes the range of data as 1st argument, 2nd argument here is the criteria range & 3rd one 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.

**Read More:**** INDEX MATCH Multiple Criteria in Excel (Without Array Formula)**

**Concluding Words**

I hope that all these methods mentioned above will now prompt you to apply them in your regular Excel chores. If you have any questions or feedback then please let me know in the comment section. Or you can check out our other useful articles related to Excel functions on this website.

**You May Also Like to Explore**

**INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)****Index Match with Multiple Matches in Excel (5 Methods)****How to use INDEX & MATCH worksheet functions in Excel VBA****Excel INDEX MATCH to Return Multiple Values in One Cell****INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)****How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results**

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