## Array Formulas in Excel

An Array Formula in excel is a formula that uses arrays instead of single cell value as input.

**Excel Array Formulas** can be thought of as many formulas packed into a single super formula. In this article we will take a look at the syntax of an array formula, its various parts, learn how to write a basic array formula and then graduate on to writing the more powerful versions of it. Before we begin, let us first understand what an “array” means – a construct at the very core of array formulas.

## What is an Array ?

An array in Excel is simply a range of cells. Given below are a few examples of arrays in Excel. Arrays in Excel can be classified as single dimensional or two dimensional. Single dimensional arrays in turn can be horizontal or vertical.

When we refer to a formula as an array formula, we mean that it is a normal Excel formula (SUM, MAX, COUNTIF etc.) but has been modified to take an array or a set of arrays as input. This is what lies at the core of array formulas and makes them so powerful.

An Array Formula has to be entered in a particular manner – a simple enter will not work. Let’s take an example. Open up a fresh worksheet and enter a set of values as shown below. Now say, if you wanted to know what is the address of the cell with the smallest value, you can simply enter the array formula given below in any cell and **press CTRL +SHIFT + ENTER**.

=ADDRESS(MIN(IF((A1:A9)=MIN(A1:A9),ROW(A1:A9),””)),1)

The result in this case will be the address of the smallest value in the given range. As you can see, the moment we update the values, the result also changes. The same result could also have been achieved with a normal excel formula like “=ADDRESS(MATCH(MIN(A1:A9),A1:A9,0),1)”. However we’ve just started to scratch the surface when it comes to the power of Array formulas and we will soon discover things that could be done in a single array formula which would have otherwise been done with a large number or even thousands of normal excel formulas. Having said that, let’s take a closer look.

## Parts of an Array Formula in Excel

Array formulas can be understood as a combination of Array Constants, Array Operators and Array Ranges. Think of it as a replacement for multiple simple formulas or as a shorthand in which a single formula is provided will all the information required to carry out a complex operation. Hence an array formula will take in arrays as a part of its arguments. Internally it will traverse each array specified as the argument, perform the required calculations and generate a single result. (Array formulas can also be entered in a range but for the sake of simplicity, more about them later.)

- (Before we go deeper in to Array formulas, please ensure that whenever you enter an array formula, it has to be entered with a CTRL + SHIFT + ENTER and not a single enter as we do for normal Excel formulas)

## Array Constant in an Array Formula

An array constant is a set of static values. These values do not refer to other cells or ranges. Therefore these will always remain the same irrespective of the changes taking place in any other cell of the worksheet or workbook.

### A Horizontal Array Constant

A horizontal array constant is entered as number separated by a comma and enclosed in brackets. Ex {1,2,3,4,5}. Horizontal arrays can be used as input to a array formula. They can also be entered into a worksheet like shown below:

### A Vertical Array Constant

A vertical array constant is entered as number separated by a semi-colon (;) and enclosed in brackets. Ex {1;2;3;4;5}. Vertical arrays can be used as input to a array formula. They can also be used to enter data into a worksheet as shown below:

## Array Operator in an Array Formula

An array operator tells the array formula what operation to perform on the arrays provided as the input. You can use the AND (used as *) and the OR (used as +) as operators.

### AND Array Operator

The AND array operator is used to return a value if and only if ALL of the conditions in a given expression evaluate to TRUE. Shown below is the use of AND array operator (*) between two arrays:

### OR Array Operator

The OR array operator is used to return a value if ANY of the conditions in a given expression evaluate to TRUE. Shown below is the use of OR array operator (+) between two arrays:

## What is an Array Range ?

An array range is entered as a normal range (Ex. A1:A10) They may or may not be immediately enclosed within a bracket. (Ex =SUM(IF((A1:A10)=10,10,””)) ) or (=SUM(IF((A1:A10=10),10,””))). However I prefer to enclose the array range in a bracket as this allows easy debugging of formulas. Since array formulas can seem a bit complicated when one uses them first, it is advisable to follow this convention.

## Syntax of an Array Formula

You might have gotten a bit of idea of this part already. All you need to write an array formula is to have the formula provided with an array (or arrays) as the argument and pressing CTRL + SHIFT + ENTER.

Let’s look at some basic Array Formulas and learn about the syntax:

#### Sorting a range using array function

Say you have a range of values in cells D2:D10. You want to sort out the values in ascending order. You might’ve guessed that we will need to you the SMALL() function which returns the smallest nth value from a given range. We will also require the range over which we want this calculation be carried out (D2:D10).

A normal SMALL function for one single cell would’ve read something like =SMALL(D2:D10,1). This would have given us the smallest value from the range D2:D10. But we would have to copy it to all 10 cells and make changes to the second argument to get a sorted list. We first select the entire range in which we want the sorted results to appear beginning from the first cell. While the entire range is selected, we enter the array function =SMALL(D2:D10,{1;2;3;4;5;6;7;8;9}) in the first cell and presss CTRL + SHIFT + ENTER. This will copy the formula to all the cells in the selection. The final result is sorted list of values.

#### Generating sub totals using array formulas

Suppose you had a set of values as given in the table below and you wanted to know the subtotal of sales against each manger’s name. We can use the function =SUMIF((A2:A10),(A2:A10),(D2:D10)) to generate the subtotals. What the function is doing essentially is to checking each Manager name in the first range (A2:A10) for matching each one against the given values in the second range (A2:A10 in this case) and if a match is found, returning the Sum of Sales over the third range (D2:D10).

We simply selected the entire range in which wanted the subtotals starting with the first cell and then simply entering the formula using CTRL + SHIFT + ENTER to generate the subtotal for the entire range.

#### Finding out a unique match from the given data set for a specific condition

Let’s say we wanted to find the name of the manager with the highest sales figures. Now this is where the power of array formulas truly comes to light. IF we were to use normal formulas, this would have required atleast as many formulas as there are managers, if not more. However we can accomplish the same in a single array formula using =OFFSET(A1,MAX(IF(SUMIF((A2:A10),(A2:A10),(D2:D10))=MAX(SUMIF((A2:A10),(A2:A10),(D2:D10))),ROW(A2:A10),””))-1,0). What we are doing here is to check if the sum of sales for a particular manager is equal to the sum of sales for the person with the highest sales. If true, then return the row number. The IF function returns an array of row numbers pertaining to the sales manager with the highest sales figures otherwise it returns a blank. Using the MAX function, we find the row where the last occurrence of the Name takes place and then using the OFFSET function, return the name from that row.

#### Aggregating data for more than one condition

We can also use the array formulas to find the sum of sales for the person with the highest sales. The IF array formula here simply returns an array consisting of the individual sales figure where the name of the manager matches with name of the person with the highest sales, otherwise 0. We then simply use a SUM function to sum up all those values in the array and get the result.

#### Another Example of aggregating data for more than one condition

Now this one is for you to try. Sum (Sales) for all records where 1) Manager =”Bill Watson” and Product = “Books” OR 2) Sales >= 500. Think over it for a minute.

We can achieve this using the following array formula =SUM(IF(((A2:A10=”Bill Wanton”)*(B2:B10=”Books”))+((D2:D10>=500)),D2:D10,0)). So what are we doing here ? We are checking for three conditions – the first two have been combined using the AND array operator (*) and the third one has been added using the OR operator (+). The result is a structured array formula where all the three conditions have been specified as argument to the IF function. The IF function in turn generates an array with the values from the fourth column when it evaluates to TRUE and 0 if it evaluates to FALSE. The result of course, is the sum of sales for the three conditions specified above.

These are some of the basic examples of using array functions. You can download a worksheet with some basic examples of **Array Formulas in Excel** here. Array functions offer a powerful way of working with data and if used judiciously can be a great time saver. Watch out for the Part II of Excel Array Formulas & Array Constants : Array Formulas in Excel.

A problem I’m having is wanting to use the calculated value as the divisor elsewhere in my spreadsheet. If the IF conditions resultin an empty set, then the cell is empty and I receive divide-by-zero errors. I haven’t found any material on how to force a zero value in there – either through further conditional logic or ???

This is something I’ll be handing off, so I’m trying to avoid VB scripting.

Thoughts?

February 4th, 2010 at 6:03 pm@mX – Can you provide a few more details and the exact formula that you are planning to use? There could be a solution specific to that situation.

February 5th, 2010 at 2:39 amRegards,

What I am trying to do is find out what place the products we sell are in compared to one another. For example I have a profits for products in columnA from A1 to A10 i want to put a formula in B1 to B10 that will tell me the order they are in from 1-10 without having to sort by column A, I want B to display if A1 is in 1st, A2 is in 3rd, A3 is in 5th, etc…

May 13th, 2010 at 1:09 pmMy requirement is i have a team with 10 team leaders and, i want a simple way that if i input a name of a team leader it should show me the list of agents reporting to that team leader. I am trying vlookup function but by default it is taking first agents name only…can you please help me

May 25th, 2010 at 2:07 pm@Pradeep – You can try a combination of Data Validation, VLOOKUP and a named range. The Data validation gets you a drop down for team leaders, the VLOOKUP gets you the selection made by the user and then the named range gets you the list of agents corresponding to the team leader.

Here’s something that may work for you:

http://www.databison.com/index.php/data-validation-list-in-excel-create-drop-down-list-using-data-validation-in-excel/

Regards,

May 28th, 2010 at 1:11 amAjay

I’m with a problem that couldnt be solved anywhere and this page was the closest i could get from the solution. Quite simple actually but pretty hard to get a conclusive info about it.

I got a field, say D3, i want to check if an entire column, say H, has a field with the same value as D3, returning true or false wether it has a field with same value or not.

not hard, i’ve tried simple stuff like =IF(D3=(H1:H200),”TRUE”,”FALSE”) but the range I insert only refers to the 1st element of the range, even if i do it as an array (ctrl+shift+enter) it still only refers to the 1st element of the range.

What am I doing wrong?

December 3rd, 2010 at 10:05 amActually i just found part of the solution with the command MATCH, however not only it doesnt return something really useful and it also doesnt work with an entire column.

December 3rd, 2010 at 10:48 am@Douglas Barbará: I believe you want a vlookup. The formula you would put (In a cell that is not D3 or in column H) is: =VLOOKUP(D3, H:H, 1, FALSE)

That will populate with the value in D3 if it can be found in column H, or #N/A if it can’t. You could expand it to the following if you want it to be TRUE/FALSE instead:

=NOT(ISNA(VLOOKUP(D3, H:H, 1, FALSE)))

Hope this is still useful to you.

December 23rd, 2010 at 2:55 pmhi ajay! i really need your help as i am in a tight spot right now…

my boss wants me to work out on a formula for excel but i am not able to…

he has given me sequences and excel should show level of the person working on

ex. 6,3,3,3,5 then its level 3

ex. 3,3,4 then its level 3

so i think its mode…

i.e. maximum frequency of number

but tricky part is when sequence is like 4,3,6,6,4 the its level 5

so i think because 4 and 6 both r highest frequency in sequence i.e. both occur twice they have taken mean of it…

so how to make formula for this

i mean to find mode but if 2 numbers occur same times then average…

kindly help

April 6th, 2011 at 3:43 amreally need it!

thanks alot!

Just getting started using Array Functions which from what I read I need to use to solve my problem. What I need to do is sum up the numberic portion of the cell, it the first Letter equal the lookup value.

Example

Column A1 = A2

Column B1 = K

Column C1 = A4

Column D1 = T6

Column E1 = R1

Column F1 = A3

I need to sum up the numeric portion of the cells that contain a value starting with an A which would be 9.

I have looked at the SumProduct and Match functions but can’t seem to get everything to work properly. Usually I get an #Value or just the 2 from column A1.

Any assistance or pointers would be helpful

August 26th, 2011 at 4:55 pmThis is just about the best explanation of Excel array formulas I have come across.

But I still despair of how to do this:

Column of symbols in the range A1:A4, say:

A

B

C

D

I want to concatenate them into the string

A+B+C+D+

The long, stupid way is

=CONCATENATE(A1&”+”&A2&”+”&A3&”+”&A4&”+”);

Is there an array formula solution?

September 13th, 2011 at 8:29 amvery, very good way to explain, may I send a question?

October 21st, 2011 at 12:07 amExcellent

November 23rd, 2012 at 5:21 pmI want to create a formula which lets me look up the total value of each agents sales for a certain product.

April 5th, 2014 at 6:00 amThe formula above does not work.

In the array formula

=ADDRESS(MIN(IF((A1:A9)=MIN(A1:A9),ROW(A1:A9),””)),1)

January 29th, 2015 at 12:29 pmif we replace the MIN that wraps IF with LARGE or SMALL the formula will still work the same.

But if the “IF” is not wrapped in SMALL, LARGE or MIN the formula gives FALSE(or anything specified).

Please elaborate why does it happen?