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.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query