ARRAY FORMULAS

Array formulas are powerful formulas in Excel that perform calculations using multiple ranges of cells. They are also known as CSE formulas because you must press CTRL + SHIFT + ENTER to tell Excel that you are inserting an array formula so that Excel can treat Range of cells as “ARRAYs”

Lets look at some of the basics. If we have a sound understanding of how Arrays work, we can understand how complex ARRAY formulas work and then we can write some creatively useful formulas on our own

An array is a collection of items (values, text etc) like {1,2,3,4,5}. If you type the formula =sum({{1,2,3,4,5}) in any cell you will get 15. If you type ={1,2,3,4,5}, Excel returns first element of the array

When two similar arrays  are operated against another, the result is another array where every “nth” item of one array is operated against every “nth” item of other array

So

{1,2,3,4,5}  + {6,7,8,9,10} returns {7,9,11,13,15}

{1,2,3,4,5}  * {6,7,8,9,10} returns {6,14,24,36,50}

{10,2,3,4,50}  > {6,7,8,9,10} returns {TRUE , FALSE , FALSE , FALSE , TRUE}

{“a”,”b”,”c”,”d”}  =  {“f”,”g”,”h”,”d”} returns {FALSE , FALSE , FALSE , TRUE}

{“a”,”b”,”c”,”d”}  &  {“f”,”g”,”h”,”d”} returns {“af”,”bg”,”bh”,”dd”}

Similarly When an array is operated against a value, each element of the array is operated against that Value resulting in a new array. So

{1,2,3,4,5} +1 returns {2,3,4,5, 6}

{1,2,3,4,5} >1 returns {FALSE , TRUE , TRUE , TRUE, TRUE}

If ({1,2,3,4,5} >1,{1,2,3,4,5},FALSE)  returns {FALSE , 2 , 3 , 4, 5}

WITH THE ABOVE UNDERSTANDING LETS WORK ON ARRAY FORMULAS in EXCEL

Click here to Download the Illustrative Examples

1) PERFORMING INDEX MATCH WITH MULTIPLE CRITERION

In this simplified example, our challenge is to find the row (using formula of course) where all 3 criterion are met. i.e Name is Zubair, Country is Pakistan, Sex is Male.

 

The Array Formula that will do the job is

=INDEX(D7:D11,MATCH(A3&B3&C3,A7:A11&B7:B11&C7:C11,0))

So how does it work. Recall from our understanding of arrays above that

A7:A11 & B7:B11 & C7:C11 should return {JunaidPakistanMale , NomanSaudiMale , ZubairIraniMale , ZubairPakistanMale , ZubairSaudiMale}

Therefore MATCH(A3&B3&C3,A7:A11&B7:B11&C7:C11,0) returns Match(ZubairPakistanMale,{JunaidPakistanMale , NomanSaudiMale , ZubairIraniMale , ZubairPakistanMale , ZubairSaudiMale},0)

so the answer is 4 (4th row of the data)

2) TESTING IF AN ITEM EXISTS IN A TABLE OR A LIST

Recall the “OR” function in Excel.

“OR” returns TRUE if at least 1 argument is TRUE

So OR (“A”=”A”,1=2) would return OR(TRUE,FALSE). The result is TRUE since one argument is TRUE

This Power of “OR” function can be used to check if an item exists in a range of cells

Example

OR (“Zubair”={“Hasan”, “Rayyan”, “John”,”Zubair”)) would return OR(False,False,False,True)

The result is TRUE since one argument is TRUE

Following is one way how I use it. The example is available in file you downloaded above

I use this Array Formula to check if an item I select from the DROP DOWN is an expense item. If an item is an EXPENSE item, it is multiplied by -1.

 

WHY? Because My company’s Information System stores expense items as “Negative” number which results in  column charts hanging from the top.

So I use this Array Formula {=IF(OR(A17=Expenses),”Expense Item”,”Income Item”)}  to “Check” if the user selection is an expense item and multiply it it with -1 so that chart shows positive numbers

Expenses is a named range. We can also use A3:C8

 

3) DETERMINE THE APPROPRIATE BAND OR GROUP BASED ON RANGE OF VALUES

Suppose HR uses following Parameters (Salary Ranges) to assign GRADES to Employees. Your objective is to determine the GRADE for the following six employees based on the parameter on the right

Try few formulas on your own before looking at the solution

 

The required array formula is (For Cell C3)

{=INDEX($K$4:$K$10,MATCH(1,(B3>=$I$4:$I$10)*(B3<=$J$4:$J$10),0))}

This is how it works

B3>=$I$4:$I$10 returns {True,False,False,False,False}

B3<=$J$4:$J$10 returns {True,True,True,True,True}

In Excel “False” is equivalent to ZERO while “True” is equivalent to 1. You can test it by writing TRUE +1 or FALSE + 1 in any Cell in Excel

So (B3>=$I$4:$I$10)*(B3<=$J$4:$J$10) return {True,False,False,False,False}* {True,True,True,True,True}

{True,False,False,False,False}* {True,True,True,True,True} = {1,0,0,0,0}*(1,1,1,1,1}= {1,0,0,0,0}

So MATCH(1,(B3>=$I$4:$I$10)*(B3<=$J$4:$J$10),0) = MATCH(1,{1,0,0,0,0},0)= 1

So   {=INDEX($K$4:$K$10, 1)} returns “A”

Please leave your feedback below.

If you have an interesting ARRAY formula to share please do share with me