Search 6,000+ CA Community College Courses

Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

YouTube

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

ExcelIsFun via YouTube

Start learning Write review

Overview

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.

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

Reviews

Start your review of Ctrl - Shift - Enter- Excel Array Formulas - Formulas to Extract Records With Criteria

Start learning

Never Stop Learning.

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

Sign up for free
Someone learning on their laptop while sitting on the floor.