There is a little known feature in Excel that can greatly simplify complex multi-range formulas. Sooner or later, everyone who works in Excel will need to create a formula that performs calculates on a range of values, based on the values in another range. For example: “Only count the employees who are in the Accounting Department.”

The most obvious “first try” is to use COUNT and IF. This will work, but if you are dealing with several values to test, the formula will become very long.

=COUNT(IF($A$1=“Cat”, $D$1, IF($A$2=”Cat”, $D$2, IF($A$3=”Cat”, $D$3, … ), 0), 0))

By using a CSE Formula you can greatly shorten this equation. CSE stands for , and just means that you must press these keys after completing the formula or making any changes. This lets Excel know that it is working with an Array Formula.

For example, the above formula can be written as:
{=SUM(IF($A$1:$A$50=”Cat”, $D$1:$D$50), 0)}

You may have noticed the curly brackets, “{}”, in the above formula. They are placed around the equation when you press . I have included this example to better illustrate the feature.

In this spreadsheet, we are trying to determine the cost of overtime. So we only want to consider the rows where the hours are greater than 40. On each line I placed the formula used at the end of the line so you can see how I got my results.

In rows 16 and 17 I am totaling the Overtime and Cost of Overtime using a CSE Formula. So, after I entered the formula, I had to press to let Excel know that this was an Array Formula. As you can see, if we had to build the formula from the IF statements in columns I and J above, we would have a very long and cumbersome result. But everything is compact and easy to read if we create an Array Formula.

Almost any Excel function can be used in an Array Formula. I would recommend you play around a bit and see what you can do, just remember to press after adding or changing things.

A very popular function in Excel is VLOOKUP. With VLOOKUP you can return a value based on a defined table and column reference.

=VLOOKUP(lookup value, table, result column #, FALSE)
Here is an example:

The equation in D11 is shown next to it, starting in E11.

VLOOKUP:

Find “Hot Pants” (value in cell B11)
In the table at B2:F8
When found, return the value in the 4th column on the same row.
*FALSE means exact match

Nice, easy to understand, and powerful. The only problem is that when you have several of these or references to a large array, it can run very slow and will eat up resources.

Another method to arrive at the same result is by using the INDEX MATCH combination.

INDEX(result column array, MATCH(lookup value, lookup column array, 0))

Here is an example:

The equation in D11 is shown next to it, starting in E11.

INDEX:

In the array at E3:E8
Show the value in the row defined by
MATCH:

Find “Hot Pants” (value in cell B11)
In the array at B3:B8
*0 since we only have 1 column select in array

A little bit more complex, but uses less resources while getting the same results.

In larger and more complex spreadsheets the difference in processing times is huge. So, although VLOOKUP is less complex, the benefits of INDEX MATCH are well worth the effort.

The best way to remember the syntax is:
=INDEX(array I want a return value from, (MATCH (the value to look for, array I want to search, 0)).