Excel Essentials: Automating the Matrix

Building a matrix in Excel is easy.  Create an x-axis category, create a y-axis category, and populate with data.  Done...until you need to extract that data.

If your matrix isn't automated, you're not much better off than you were before.  Your data may be better organized but your data processing time remains virtually unchanged.  Manually evaluating a matrix is akin to walking a bicycle: you have a vehicle whose full potential is not being used.  Let's look at two methods that will quickly get you from point A to point B.

Exact Match Matrix

Suppose you're conducting research on annual median income by state.

In this example, you can easily see that the median income in Wisconsin in 2012 was $53,079.  But if your matrix consists of incomes from all 50 states over the span of the last 25 years, your data volume becomes much larger, as does your margin of error if you're looking up the values by hand.  Let's automate this process by using the INDEX and MATCH functions.

The INDEX function can be used to retrieve the data from the matrix.  Note that the x and y axes of the matrix are not included in the input range of the INDEX function.  The first argument should only consist of the data that can be returned.  Now that the return range is defined, we need to instruct the INDEX function which value to return from the data set.

The first MATCH function tells the INDEX function which row contains the median income value that we're looking for.  This function looks at the value in G5 and finds its match in the range of values in A4:A7.  The zero in the third argument of the MATCH function tells it to find an exact match.

Likewise, the second MATCH function tells the INDEX function which column contains the median income value that we're looking for.  This function looks at the value in G4 and finds its match in the range of values in B3:D3.  Again, the zero in the MATCH function indicates that we're looking for an exact match.

This approach works well because the values in the x and y axes are exact values.  But what if the values that you're looking up fall within a range?

Range Matrix

Let's say that you're a sales manager responsible for calculating your team's bonuses.  Reps receive a bonus based on an incrementing scale of gross sales and number of service plans sold.

Using a number range for a header, such as "100,000-199,999", is a common misstep when creating range matrix criteria.  It's better to use a single number instead of a range.  The header "100,000" acts as a threshold and allows for easier automation.  Think of range matrices as an "at least" scenario.  A sales rep needs to achieve at least $100,000 in gross sales and sell at least 10 service plans to receive a $150 bonus.

The formula is exactly the same with one important exception; the third argument in the MATCH functions has been changed from zero to one.  The MATCH functions are still evaluating the same cells in the same cell ranges. The difference is that the functions are now looking for where the variables fall within the ranges rather than looking for an exact match.

Automation not only improves your data accuracy, but it also increases your productivity by saving you time.  Now you can spend less time working and more time riding that bicycle!