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.
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
- Formula Syntax:
=INDEX(array, row_num, [column_num])
=INDEX(reference, row_num, [column_num], [area_num])
Returns a value of reference of the cell at the intersection of the particular row and column, in a given range.
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.
➤ In Cell F19, type:
➤ 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.
- Formula Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specified order.
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.
➤ In Cell F17, our formula will be:
➤ 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:
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.
➤ 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:
Finally, we’ll determine which computer brand is offering the lowest price. So, in Cell H10, the formula will be:
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.
➤ Select Cell H7 & type:
➤ 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])
=AGGREGATE(function_num, options, ref1, ref2, [ref3],…)
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.
➤ 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-
➤ 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 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.
➤ 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.
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.