INDEX and MATCH functions are two of the most common functions in Excel chores to search for as well as store different data from a large array. In this article, youâ€™ll be able to know with proper illustrations how we can use these INDEX & MATCH functions to find minimum or smallest values in Excel.

**Table of Contents**hide

**Download Excel Workbook**

You can download the Practice Workbook that weâ€™ve used to prepare this article. You can change or modify data & find new outputs accordingly.

**Introduction to INDEX & MATCH Functions**

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

Assuming that we have a table comprising of the names of different brands, device types, and the related sales prices for 6 months. We want to know the sales price at the intersection of the 3rd row & 4th column from the array of selling prices.

**ðŸ“Œ**** Steps:**

âž¤ In **Cell F19**, type:

`=INDEX(D5:I14,3,4)`

âž¤ Press **Enter** & youâ€™ll get the result.

Since the 4th column in the array represents the selling prices of all devices for April & the 3rd row represents the Lenovo Desktop category, so at their intersection in the array, weâ€™ll find the selling price of Lenovo Desktop in April.

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

Based on the same dataset already mentioned, first of all, here weâ€™re going to know the position of the month June from the month headers.

**ðŸ“Œ**** Steps:**

âž¤ In **Cell F17**, our formula will be:

`=MATCH(F16,D4:I4,0)`

âž¤ Press **Enter **& youâ€™ll find that the column position of the month June is 6 in the month headers.

Change the name of the month in **Cell F17 **& youâ€™ll see the related column position of another month selected.

And if we want to know the row position of the brand Dell from the names of the brands in **Column B**, then the formula in **Cell F20** will be:

`=MATCH(F19,B5:B14,0)`

Here, **B5:B14** is the range of cells where the name of the brand will be looked for. If you change the brand name in **Cell F19**, youâ€™ll get the related row position of that brand from the selected range of cells.

**4 Suitable Approaches to Find Minimum Value with INDEX-MATCH Formula**

**1. Using INDEX, MATCH & MIN Functions Together to Get the Lowest Price**

Letâ€™s get introduced to our original dataset now which has been considered for the applications of** INDEX & MATCH** functions to extract minimum value. There are some random computer brands, their devices, model names & prices in the table. Weâ€™ll find out first which price is the lowest among all in **Column E**.

**ðŸ“Œ**** Steps:**

âž¤ Select output **Cell H7** & type:

`=INDEX($B$5:$E$14, MATCH(MIN($E$5:$E$14), $E$5:$E$14, 0 ), 4 )`

âž¤ Press **Enter **& youâ€™ll get the lowest price at once.

**ðŸ”Ž**** How Does This Formula Work?**

âž¤ **MIN** function here pulls out the smallest or minimum value from the range of **Cells E5:E14**.

âž¤ **MATCH** function finds out the row position of that minimum value.

âž¤ Within** the INDEX **function, **B5:E14** 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 model name or number for the lowest price. So in our output **Cell H8**, the related formula will be:

`=INDEX($B$5:$E$14, MATCH(MIN($E$5:$E$14), $E$5:$E$14, 0 ), 3 )`

Here, 3 is the column number for the **INDEX** function as all model names are present in the 3rd column of the selected array.

In **Cell H9**, weâ€™ll find out the type of device for the lowest price & here, the column number will be 2. So, the embedded formula will be in **Cell H9:**

`=INDEX($B$5:$E$14,MATCH(MIN($E$5:$E$14),$E$5:$E$14,0),2)`

Finally, weâ€™ll determine which computer brand is offering the lowest price. So, in **Cell H10**, the formula will be:

`=INDEX($B$5:$E$14,MATCH(MIN($E$5:$E$14),$E$5:$E$14,0),1)`

**2. Using INDEX, MATCH & MIN Functions Together to Find the Earliest 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 which date is the earliest among all.

**ðŸ“Œ**** Steps:**

âž¤ Select **Cell H7** & type:

`=INDEX($B$5:$E$14,MATCH(MIN($E$5:$E$14),$E$5:$E$14,0),4)`

âž¤ Press **Enter** & now youâ€™ll find the earliest date among all instantly.

As the dates are present in the 4th column of the selected array, so 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.

**3. Combining INDEX, MATCH & AGGREGATE Functions to Determine Lowest 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 look at **Column B** & youâ€™ll find some names multiple times. So, we now want to find out the lowest price for a specified brand device. Assuming, weâ€™re going to determine the lowest price of the desktop of Computezo brand.

**ðŸ“Œ**** Steps:**

âž¤ Select the output **Cell H10** & type:

`=AGGREGATE(15, 6, E5:INDEX(E:E, MATCH(1E+99,E:E))/ ((B5:INDEX(B:B, MATCH(1E+99,E:E ))=H8)*(C5:INDEX(C:C, MATCH(1E+99,E:E ))=H9)),1)`

âž¤ Press **Enter** & youâ€™ll get the lowest price of Computezo desktop at once.

**ðŸ”Ž**** How Does This Formula Work?**

âž¤ Inside the array, 15 is the **Function Number** that is assigned to the **SMALL** 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-

**{1050;1200;980;950;1160;960;1020;980;1350;1050;1070;1020}**

âž¤ The divisor or denominator has two parts & both work with logical functions. The 1st part looks for the brand name Computezo in **Column B** & 2nd part looks for a desktop device in **Column D**. Then the converted** (TRUE=1, FALSE=0)** & numerical logic values are multiplied alongside these two parts. So, the resultant array returns as-

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

âž¤ Now, all the prices found from the dividend will be divided with these logical values found from the divisor & itâ€™ll return as-

**{1050;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;960;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1070;#DIV/0!}**

âž¤ Finally, the **AGGREGATE** function ignores all the **Error Values** & extracts the smallest value from the array.

**4. Fusing SMALL Function with INDEX-MATCH to Find the Lowest or Smallest Three**

In our final method, weâ€™ll apply the **SMALL** function which extracts the smallest value from a range of cells or an array based on the defined position or serial of the smallest value. In our dataset below, prices of different models of 4 computer brands are present. Weâ€™ll find out the lowest three prices of a specific brand, let it be Clocktech.

**ðŸ“Œ**** Steps:**

âž¤ In **Cell E7**, our formula for the mentioned criteria will be:

`=SMALL(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 lowest price of the Clocktech brand.

âž¤ To get the 2nd & 3rd lowest 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 lowest 3 prices of that brand.

**Concluding Words**

So, these are all suitable ways to find minimum or smallest values from a range of cells or an array based on different criteria. If you think Iâ€™ve missed a point that I shouldâ€™ve added to then please let me know in the comment section. Or you can check out our other interesting & informative articles on this website too.

**You May Also Like to Explore**

**SUMPRODUCT with INDEX and MATCH Functions in Excel**

**Index Match Multiple Criteria in Rows and Columns in Excel**

**Index Match with Multiple Matches**