**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 the **minimum or smallest value** in Excel. In addition, for conducting the session, we’re going to use **Microsoft 365 version**.

## Download Excel Workbook

You can download the Practice Workbook that we’ve used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.

## Introduction to INDEX & MATCH Functions

In this section, we will introduce the **INDEX** and **MATCH** functions for your better understanding of the whole session.

### INDEX Function

**Formula Syntax:**

**=INDEX(array, row_num, [column_num])**

or,

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

**Activity:**

Gives a value from the given array at the intersection cell of the mentioned row and column number.

**Example:**

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

**📌**** Steps:**

- Firstly, in
**Cell F19**, type:

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

- Secondly, press
**ENTER**& you’ll get the result.

Here, the **4th column** in the array represents the selling prices of all devices for **April **& the **3rd row** represents the** Lenovo Desktop** category, at their intersection in the array** D5:I14**. Thus, we’ll find the selling price of **Lenovo Desktop in April**.

### MATCH Function

**Formula Syntax:**

**=MATCH(lookup_value, lookup_array, [match_type])**

**Activity:**

Gives the relative position of an item in a given array that matches with a certain value in a fixed order.

**Example:**

Here, based on the same dataset already mentioned, first of all, we’re going to know the position of the month of **June **from the month headers.

**📌**** Steps:**

- Firstly, in Cell
**F17**, our formula will be:

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

- Subsequently, press
**ENTER**& you’ll find that the column position of the month of**June**is**6**in the month headers.

Now, change the name of the month in **Cell F16** & you’ll see the related column position of that month.

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 in Excel

Here, I will demonstrate **4 suitable methods **with detailed steps on how to use these **INDEX** & **MATCH** functions to find the **minimum or smallest values** in Excel. Now, let’s see the methods.

### 1. Using INDEX, MATCH & MIN Functions Together to Get Minimum 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**. Basically, there are some random computer brands, their devices, model names & prices in the table. Now, we’ll find out first which price is the** lowest** among all in **Column E**.

**📌**** Steps:**

- Firstly, select output
**Cell H7**& type:

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

- Secondly, press
**ENTER**& you’ll get the**lowest**price at once.

**🔎**** How Does This Formula Work?**

- Firstly,
**the MIN function**here pulls out the**smallest**or**minimum**value from the range of Cells**E5:E14**. - Secondly, the
**MATCH**function finds out the**row position**of that minimum value. - Thirdly, 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**. - Fourthly,
**‘4’**has been chosen as a**column number**since the price list is present in the**4th**column of the selected array. - Lastly, the
**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.

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

**Read More:** **Examples with INDEX-MATCH Formula in Excel (8 Approaches)**

**Similar Readings**

**INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)****How to Use INDEX and Match for Partial Match (2 Ways)****INDEX MATCH with 3 Criteria in Excel (4 Examples)****Index Match Sum Multiple Rows in Excel (3 Ways)**

### 2. Using INDEX, MATCH & MIN Functions Together to Find Earliest Date

Moreover, 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. Now, we’ll find out which date is the **earliest** of all.

**📌**** Steps:**

- Firstly, select
**Cell H7**& type:

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

- Secondly, 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.

Here, you can notice the following image, then you will understand the whole scenario.

**Read More:** **INDEX, MATCH and MAX with Multiple Criteria in Excel**

### 3. Combining INDEX, MATCH & AGGREGATE Functions to Determine Minimum 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:**

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

- Secondly, press
**ENTER**& you’ll get the**lowest price**of**Computezo**desktop at once.

**🔎**** How Does This Formula Work?**

- Firstly, inside the array,
**15**is the**Function Number**that is assigned to**the SMALL function**. - Secondly,
**6**has been chosen as**Option Number**which ignores the**Error Values**. - Thirdly, 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}**

- Fourthly, 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.

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

### 4. Fusing SMALL Function with INDEX-MATCH to Find Minimum 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. Now, we’ll find out the **lowest three prices **of a specific brand, let it be **Clocktech**.

**📌**** Steps:**

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

- Secondly, press
**ENTER**& you’ll see the**1st lowest price**of the**Clocktech**brand.

- Now, to get the
**2nd & 3rd lowest**prices, use the**Fill Handle**icon to fill down**Cells E18 & E19**.

As a result, you will find the** 3 lowest prices **of the **Clocktech **brand. Furthermore, in **Cell E16**, if you change the brand name, you’ll find the **lowest 3 prices** of that brand.

## How to Use VLOOKUP Function to Find Minimum Value in Excel

Here, you can use **the VLOOKUP function** along with the **MIN** function to find the minimum value instead of the **INDEX-MATCH** formula in Excel. Additionally,** one **important thing, in the case of using the **VLOOKUP** function you have to keep the **reference column 1st**. So, we modified our dataset to keep the **Price column 1st**. Now, follow the steps below-

**📌**** Steps:**

- Firstly, let’s find the
**lowest price**. So, you may select the**H7**cell. - Secondly, you need to use the corresponding formula in the
**H7**cell.

`=VLOOKUP(MIN(B5:B14),B5:E14,1,FALSE)`

- Subsequently, you need to press
**ENTER**to get the value.

**🔎**** How Does This Formula Work?**

**VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])**

- Firstly,
**MIN(B5:B14):**here the**MIN**function will extract the**lowest**value which will be the**lookup_value**for the**VLOOKUP**function. - Secondly,
**B5:E14**is the**table_array**in which it looks for the**lookup_value**in the leftmost column. - Thirdly,
**1**is the number as**column index**from which column a value is to be returned. - Finally,
**FALSE**denotes an exact match.

- Now, write a similar formula to find out the model name. Here, you must change the
**column index number**. Which will be**4**. So, the formula should be as below,

`=VLOOKUP(MIN(B5:B14),B5:E14,4,FALSE)`

- Similarly, by changing the
**column index number**you can get the device and brand name too.

## Practice Section

Now, you can practice the explained method by yourself.

## Concluding Words

So, these are all suitable ways to find the **minimum or smallest value **from a range of cells or an array based on different criteria using the** INDEX-MATCH** formula. If you think we’ve missed a point that we should’ve added, then please let us know in the comment section. Or you can check out our other interesting & informative articles on this **Exceldemy** website too.

**You May Also Like to Explore**

**Index Match with Multiple Matches in Excel (5 Methods)****Excel Index Match single/multiple criteria with single/multiple results****How to use INDEX & MATCH worksheet functions in Excel VBA****Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)****How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results**