When you are working with large data sets, Excel’s built-in filters are a lifesaver, letting you get straight to the sub-set of data that you need. Sometimes, though, you need to be able to pull a set of data dynamically based on criteria that change. When the filter conditions change often, Excel’s filters fall short. Instead, there is a set of functions that can extract a data table from a larger data set based on specific criteria that you set. This tutorial will show you how to list values from a table based on filter criteria using sub-arrays and the SMALL function.
NOTE: The following article is a thorough breakdown of the individual pieces of a complicated array formula in Excel. If you just want to cut to the chase and look at the actual code, click the Using the Sub-Array Formula link in the table of contents.
For this exercise, we are going to use the 2014 auto fuel economy data from the EPA. This is the same data set used in the VLOOKUP with multiple criteria tutorial. In that exercise, we wanted to identify the fuel economy of a specific vehicle. Now, we are going to try to list all the vehicles that meet a minimum miles per gallon (MPG) rating.
The data is organized by Make, Model, Engine Displacement, Number of Cylinders, and the MPG ratings for City, Highway, and Combined mileage.