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.
Gives a value from the given array at the intersection cell of the mentioned row and column number.
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.
- Firstly, in Cell F19, type:
- 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.
Gives the relative position of an item in a given array that matches with a certain value in a fixed order.
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.
- Firstly, in Cell F17, our formula will be:
- 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:
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.
- 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:
Finally, we’ll determine which computer brand is offering the lowest price.
- So, in Cell H10, the formula will be:
- 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.
- Firstly, select Cell H7 & type:
- 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.
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.
- 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
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.
- 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-
- 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-
- Now, all the prices found from the dividend will be divided with these logical values found from the divisor & it’ll return as-
- 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 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.
- 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-
- 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.
- Subsequently, you need to press ENTER to get the value.
🔎 How Does This Formula Work?
- 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,
- Similarly, by changing the column index number you can get the device and brand name too.
Now, you can practice the explained method by yourself.
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