Understand how you can find the Text that fulfill Minimum and Maximum criteria from next given column.
In the first Example you will evaluate Minimum example in detail and the we will quickly wrap up the Max example as well.
Download Your Example Excel File
In both example we will be creating our formula with Index and Match along with MIN or MAX function in Excel:
- MATCH: Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
- INDEX: Returns the value at a given position in a range or array. You can use the index to retrieve individual values or entire rows and columns.
- MIN: The Excel MIN function returns the smallest numerical with the lowest value as per given reference of cell range.
Formula Explanation (Step by Step)
Excel start calculating from the innermost parenthesis and then work outward. You can also assess debug formula using shortcut Key F9 function key.
Let’s break the formula into multiple small steps to understand its application.
Syntax: =MIN (number1, [number2], …)
Returns minimum value in the given range (C3 to C11)
Syntax: MATCH(lookup_value, lookup_array, [match_type])
It returns the position of lookup value, Cell C9 (9.7) is 7th in the given range (C3 to C11).
Syntax: INDEX(array, row_num, [column_num]
Step 3: INDEX($B$3:$B$11,7)
Find Name of position Seven in the given range (B3 to B11).
Find Name that with Maximum Value
All steps are followed as for Minimum example above the only change is Maximum function in the formula.
Formula Applied to Get ‘Maximum’
Result: Sarah ‘Monday
It first gives the Max (20.5) value in the range (C3 to C11) then Match returns the position (6) of lookup value and now Index will return value (Sarah) of the given position.