How to Combine INDEX and AGGREGATE Functions in Excel

Overview of INDEX Function

Description:

It returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Generic Syntax:

INDEX (array, row_num, [column_num])

Argument Description:

ARGUMENT	REQUIREMENT	VALUE
array	Required	Pass a range of cells, or an array constant to this argument
row_num	Required	Pass the row number in the cell range or the array constant
col_num	Optional	Pass the column number in the cell range or the array constant

Note:

If you use both the row_num and column_num arguments, the INDEX function will return the value from the cell at the intersection of the row_num and column_num.
If you set row_num or column_num to 0 (zero), you will get the whole column values or the whole row values respectively in the form of arrays. You can insert those values into cells using Array Formula.

Overview of AGGREGATE Function

Description:

The AGGREGATE function is used on different functions like AVERAGE, COUNT, MAX, MIN, SUM, PRODUCT, etc., with the option to ignore hidden rows and error values to get certain results.

Generic Syntax:
- Syntax with References:

AGGREGATE(function_num, options, ref1, ref2, …)

- Syntax with Array Formula:

AGGREGATE(function_num, options, array, [k])

Arguments Description:

Arguments in the Reference form,

function_num = Required, operations to perform. There are 19 functions available to perform with the AGGREGATE function. Individual numbers define each function. (see the table below)

Function Name	Function Number
AVERAGE	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
STDEV.S	7
STDEV.P	8
SUM	9
VAR.S	10
VAR.P	11
MEDIAN	12
MODE.SNGL	13
LARGE	14
SMALL	15
PERCENTILE.INC	16
QUARTILE.INC	17
PERCENTILE.EXC	18
QUARTILE.EXC	19

options = Required, values to ignore. There are 7 values each representing the option to ignore while performing the operations with the functions defined.

Option Number	Option Name
0 or omitted	omit nested AGGREGATE and SUBTOTAL functions
1	omit hidden rows, nested SUBTOTAL, and AGGREGATE functions
2	leave out error values, nested SUBTOTAL, and AGGREGATE functions
3	omit hidden rows, error values, nested SUBTOTAL, and AGGREGATE functions
4	leave out nothing
5	leave out hidden rows
6	omit error values
7	Ignore hidden rows and error values

ref1 = Required, the first numeric argument for functions to perform the operation. It could be one single value, array value, cell reference, etc.

ref2 = Optional, it could be numeric values from 2 to 253

Arguments in the Array Formula,

function_num = (as discussed above)

options = (as discussed above)

array = Required, range of numbers or cell references based on what the functions will perform.

[k] = Optional, this argument is needed only when performing with the function number from 14 to 19 (see the function_num table).

Return Value

Return values based on the function specified.