Finishing the Output Table
Now that we have built the sub-array formula, we can drag it across our output table headers and then down the sheet. We have to guess how many rows we need, since the list won’t go further than the number of rows we build even if there are more entries. I chose about 40 rows for the example.
Now you should be able to see the output of our sub-array formula. It lists only the items from our original table that meet the minimum MPG criteria.
If we change the criteria from 45 to 40, more vehicles qualify, so the list grows!
Using these guidelines, you should be able to create a dynamically updated filtered data table based on the criteria of your choice. Happy filtering!
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. You can read more of his writing on his personal blog at NapkinMath.io.
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