Dive deep into advanced Excel techniques with this comprehensive video tutorial on array formulas for extracting records with specific criteria. Learn why and when to use formulas instead of filters, understand the complexities of record extraction formulas, and master various methods including helper columns, INDEX and SMALL functions, and the AGGREGATE function. Explore efficient ways to handle AND and OR criteria, compare performance of different approaches with large datasets, and discover how to extract unique records across multiple lists. Gain practical skills in creating powerful, flexible formulas for data analysis and problem-solving in Excel, applicable across all versions of the software.
Ctrl - Shift - Enter- Excel Array Formulas - Formulas to Extract Records With Criteria
ExcelIsFun via YouTube
Overview
Syllabus
min) Why use formulas to extract records from table, when there is the Filter feature?.
min) Formulas to extract records often contain a large number of ranges & calculations & therefore may significantly increase spreadsheet calculation time..
min) Filter for extracting records with AND Criteria..
min) Advanced Filter for extracting records with AND Criteria..
min) Why formulas for extracting records are so complicated..
min) Helper column non-Array Formula for extracting records with AND Criteria..
min) Array formula using INDEX & SMALL functions for extracting records with AND Criteria..
min) NEVER use IFERROR for these formulas!!!!.
min) Let's remind ourselves about how the Excel 2010 AGGREGATE function works (not available in earlier versions)..
min) Array formula using INDEX & AGGREGATE functions for extracting records with AND Criteria..
min) Timing huge data set for Helper Column, INDEX & AGGREGATE functions, INDEX & SMALL functions, and the use of IFERROR..
min) Efficient & Inefficient use of IFERROR function..
min) Display extracted records vertically using ROWS function or horizontally using COLUMNS function..
min) Filter for extracting records with OR Criteria..
min) Advanced Filter for extracting records with OR Criteria..
min) Example 1: Non-Array Formula with helper column for extracting records with OR Criteria..
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..
min) Timing formulas for extracting records with OR Criteria: Helper Column, Boolean OR Calculation or MATCH function with Exact Match?.
min) Array formula using INDEX & AGGREGATE functions for extracting records in a Two-Way Lookup with OR and AND Criteria..
min) Extract Names in List 2 that are NOT in List 1: Helper Column..
min) Extract Names in List 2 that are NOT in List 1: 2010 Array Formula..
min) Extract Names in List 2 that are NOT in List 1: 2003 Array Formula..
min) Use Helper Column in Data Extraction Area when you can't have one in Data Area..
Taught by
ExcelIsFun
Related Courses
-
Ctrl - Shift - Enter- Excel Array Formulas - Extract Unique Lists & Sorting Formulas
-
Ctrl - Shift - Enter - Excel Array Formulas - Review Formula Basics
-
Extract Records With Multiple Contains - Partial Text Criteria: 4 Examples
-
Aging Accounts Receivable Reports on Multiple Sheets With Array Formula
-
Ctrl - Shift - Enter - Excel Array Formulas - Boolean Logic, AND & OR Criteria, Convert TRUE FALSE
-
Ctrl - Shift - Enter- Excel Array Formulas - Unique Count Formulas- FREQUENCY or COUNTIF Function?
