There are often times in Excel you want to look up a value in a table based on another cell. Using VLOOKUP or HLOOKUP is a popular method for doing this quickly and easily. (Or, perhaps you read about my improved INDEX-MATCH solution in a previous blog post.) Either way, these solutions are limited to only returning one match. If there are multiple matches, it simply returns the first match only. So what if you need to see multiple matches? You need to be introduced to using Array Formulas in Excel–or as I like to call it: The Dark Side Of Excel.
Array Formulas in Excel are like normal formulas, but they are designed to work off a series of cells rather than single cells. With Array Formulas you can perform normal Excel operations on specified cell ranges. For example, you can use it to quickly create totals and subtotals on columns.
Say you have a spreadsheet of animals and their corresponding name. You want to be able to input “Dog” and get a list of all the dogs on your spreadsheet. Here are steps to use an Array Formula to return multiple values that match a lookup value in a list:
- Fill Column A with the lookup reference you want to match against. In this example, we’ll use type of Animal.
- Fill Column B with values you want the formula to return. If this example, we’ll use Name.
- Enter this formula:
- Make sure you press CONTROL-SHIFT-ENTER in the cell to convert it to an Array Formula!
- Copy the formula down to allow it to list the subsequent matches, if any.
How does it work? Well, the SMALL() section of the Array Formula identifies the row numbers that match your selection. It uses the row number to return the value in that row only. When you copy the formula down, it automatically jumps to the next matching row number and returns that value, until it can’t find anything and errors out (resulting in a blank cell).
If you want to play with this example, download the sample spreadsheet. Some tips:
- Every time you modify an Array Formula, you must press CONTROL-SHIFT-ENTER so Excel knows it is an Array Formula
- If you insert a row or column, it will probably break your Array Formula and you will have to recreate it
- Array Formulas take a lot of processing power, so be careful using them on large sets of data
Array Formulas are very powerful once you wrap your head around how to use them. Enjoy!