How to Combine INDEX and AGGREGATE Functions in Excel

This tutorial will demonstrate how to use combine INDEX and AGGREGATE Functions in excel. We will use the INDEX function to find certain values from different sheets and then execute our desired criteria to ease our work. We will also use the AGGREGATE function to return aggregate in a list of databases. So, we need to how to use combine INDEX and AGGREGATE Functions in excel.


Download Practice Workbook

You can download the practice workbook from here.


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:

  • Firstly, 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.
  • Then, if you set row_num or column_num to 0 (zero), then you will get the whole column values or the whole row values respectively in the form of arrays. So, 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 that 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.


2 Easy Examples of Combining INDEX and AGGREGATE Functions in Excel

If you follow the steps correctly, you should learn how to use index aggregate excel on your own. We will do this by the following steps.


1. Using INDEX and AGGREGATE Functions for Calculating Sum in Excel

In this case, we will combine INDEX and AGGREGATE Functions in excel to calculate the total sum of given data in a certain period. We will do this by the following steps.

Steps:

  • First, arrange a dataset like the following image.

Dataset to Combining INDEX and AGGREGATE Functions in Excel

  • Next, in the C13 cell insert the name of the desired customer similar to the following image.

Input for Combining INDEX and AGGREGATE Functions in Excel

  • After that, in the C14 cell insert the following formula.
=AGGREGATE(9,0,INDEX(C6:E11,,MATCH(C13,C5:E5,0)))

Insert Formula for Combining INDEX and AGGREGATE Functions in Excel

  • Finally, you will get the desired result similar to the following image.

Final Result for Combining INDEX and AGGREGATE Functions in Excel

🔎 How Does the Formula Work?

  • MATCH(C13, C5:E5,0): this portion represents the selected range of the cell you want to find a match from.
  • INDEX(C6:E11, MATCH(C13, C5:E5,0)): This portion represents the count function the formula will work on.
  • AGGREGATE(9,0,INDEX(C6:E11,,MATCH(C13,C5:E5,0)))): This portion represents the whole condition.

Read More: How to Aggregate Data in Excel (3 Easy Ways)


2. Combine INDEX and AGGREGATE Functions for Multiple Criteria

Now, we want to combine INDEX and AGGREGATE Functions in excel to find certain values with multiple criteria. We can do this by following the below steps.

Steps:

  • At first, arrange a dataset like the following image.

Arranging Dataset for Combining INDEX and AGGREGATE Functions in Excel

  • Then, add one extra column in column E to show the output values similar to the following image.

  • Afterward, in the D4 cell insert the following formula.
=COUNTIF($B$5:$B$12,$E$5)

Insert Formula for Combining INDEX and AGGREGATE Functions in Excel

  • Furthermore, insert the desired division’s name in cell E5 and it will show the number of results in cell D4 similar to the following image.

  • Next, in the E6 cell insert the following formula.
[email protected](ROWS($D$5:D5)<=$D$4,INDEX($C$5:$C$12,AGGREGATE(15,3,($B$5:$B$12=$E$5)/($B$5:$B$12=$E$5)*(ROW($B$5:$B$12)-ROW($B$4)),ROWS($D$5:D5))),"")

  • Hence, if you press the Enter button, you will get the result for the cell. Afterward, use the Fill Handle to apply the formula to all the desired cells similar to the following image.

  • Now, you will get the desired result similar to the following image.

  • Finally, if you make changes in cell C5 then you will get the result accordingly.

🔎 How Does the Formula Work?

  • ROWS($D$5:D5): The first portion of this equation represents the reference cell.
  • AGGREGATE(15,3,($B$5:$B$12=$E$5)/($B$5:$B$12=$E$5)*(ROW($B$5:$B$12)-ROW($B$4)), ROWS($D$5:D5)): this portion represents the selected range of the database you want to use or find a match from.
  • INDEX($C$5:$C$12, AGGREGATE(15,3,($B$5:$B$12=$E$5)/($B$5:$B$12=$E$5)*(ROW($B$5:$B$12)-ROW($B$4)), ROWS($D$5:D5))): This portion represents the function the formula will work on.
  • IF(ROWS($D$5:D5)<=$D$4,INDEX($C$5:$C$12,AGGREGATE(15,3,($B$5:$B$12=$E$5)/($B$5:$B$12=$E$5)*(ROW($B$5:$B$12)-ROW($B$4)),ROWS($D$5:D5))),””): So, the total portion represents the whole condition.

Read More: How to Use Excel AGGREGATE Function with Multiple Criteria


Things to Remember

  • Firstly, we want to draw your attention to the fact that, among all the methods, using the first method is the easiest to create and most efficient way to use it in all situations.
  • Moreover, no matter which method you have used, just learn it well enough to understand its formula and final output.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to use combine INDEX and AGGREGATE Functions in excel. Moreover, we will be glad to know if you can execute the task in any other way. Furthermore, we want to add that we will be glad if you follow the ExcelDemy website for more articles like this. Hence, feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. So, we will try our level best to solve the problem or work with your suggestions.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo