Ctrl - Shift - Enter- Excel Array Formulas - Formulas to Extract Records With Criteria

Ctrl - Shift - Enter- Excel Array Formulas - Formulas to Extract Records With Criteria

ExcelIsFun via YouTube Direct link

min) Why use formulas to extract records from table, when there is the Filter feature?

1 of 23

1 of 23

min) Why use formulas to extract records from table, when there is the Filter feature?

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

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.