Completed
min) Array formula using INDEX & AGGREGATE functions for extracting records with AND Criteria.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Ctrl - Shift - Enter- Excel Array Formulas - Formulas to Extract Records With Criteria
Automatically move to the next video in the Classroom when playback concludes
- 1 min) Why use formulas to extract records from table, when there is the Filter feature?
- 2 min) Formulas to extract records often contain a large number of ranges & calculations & therefore may significantly increase spreadsheet calculation time.
- 3 min) Filter for extracting records with AND Criteria.
- 4 min) Advanced Filter for extracting records with AND Criteria.
- 5 min) Why formulas for extracting records are so complicated.
- 6 min) Helper column non-Array Formula for extracting records with AND Criteria.
- 7 min) Array formula using INDEX & SMALL functions for extracting records with AND Criteria.
- 8 min) NEVER use IFERROR for these formulas!!!!
- 9 min) Let's remind ourselves about how the Excel 2010 AGGREGATE function works (not available in earlier versions).
- 10 min) Array formula using INDEX & AGGREGATE functions for extracting records with AND Criteria.
- 11 min) Timing huge data set for Helper Column, INDEX & AGGREGATE functions, INDEX & SMALL functions, and the use of IFERROR.
- 12 min) Efficient & Inefficient use of IFERROR function.
- 13 min) Display extracted records vertically using ROWS function or horizontally using COLUMNS function.
- 14 min) Filter for extracting records with OR Criteria.
- 15 min) Advanced Filter for extracting records with OR Criteria.
- 16 min) Example 1: Non-Array Formula with helper column for extracting records with OR Criteria.
- 17 min) Array formula for extracting records with OR Criteria (3 examples). Example2: AGGREGATE and Boolean. Example 3: AGGREGATE and MATCH. Example 4: SMALL and Boolean.
- 18 min) Timing formulas for extracting records with OR Criteria: Helper Column, Boolean OR Calculation or MATCH function with Exact Match?
- 19 min) Array formula using INDEX & AGGREGATE functions for extracting records in a Two-Way Lookup with OR and AND Criteria.
- 20 min) Extract Names in List 2 that are NOT in List 1: Helper Column.
- 21 min) Extract Names in List 2 that are NOT in List 1: 2010 Array Formula.
- 22 min) Extract Names in List 2 that are NOT in List 1: 2003 Array Formula.
- 23 min) Use Helper Column in Data Extraction Area when you can't have one in Data Area.
