BloomTech’s Downfall: A Long Time Coming

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

YouTube

Extract Records With Multiple Contains - Partial Text Criteria: 4 Examples

ExcelIsFun via YouTube

Start learning Write review

Overview

Limited-Time Offer: Up to 75% Off Coursera Plus!
7000+ certificate courses from Google, Microsoft, IBM, and many more.
India: 75% Off World: 40% Off
Learn how to extract records with multiple partial text criteria in Excel using various array formulas and functions like ISNUMBER, SEARCH, LOOKUP, MMULT, ROW, and SUM. The course covers creating helper columns, using advanced filters, and understanding matrix multiplication. The teaching method involves step-by-step examples and explanations. This course is intended for individuals looking to enhance their Excel skills, particularly in data extraction and manipulation.

Syllabus

) Introduction to Contains Criteria (Partial Text Criteria) in an OR Logical Test, when search for records with Multiple Contains Criteria..
) #1 Filter & Helper Column with Formula that uses OR, ISNUMBER & SEARCH functions. This Array Formula requires Ctrl + Shift + Enter..
) #2 Filter & Helper Column with Formula that uses ISNUMBER, LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter..
) #3 Easiest solution: Advanced Filter with Formula Criteria, where formula uses LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter..
) #4: Array formula #1 to count matching records using an OR Logical Test with Multiple Contains Criteria. Use SEARCH function with two different size Function Argument Array Operations that require that we flip one of the ranges using the TRANSPOSE Array Function. Also see the functions: ISNUMBER, MMULT, ROW and SUM. This Array Formula requires Ctrl + Shift + Enter..
) Introduction to Matrix Multiplication, the Excel MMULT Array Function and detailed explanation of the mechanics of how it works in Array Formula to add all the numbers for each row in a two-way array and produce a single column of totals. This Array Function requires Ctrl + Shift + Enter..
) #4: Array formula #2 to extract records. See the functions: SEARCH, TRANSPOSE, ISNUMBER, MMULT, ROW, IF, ROWS, INDEX and SMALL. This Array Formula requires Ctrl + Shift + Enter..
) Test Array Formulas when source data changes..
) Summary..

Taught by

ExcelIsFun

Reviews

Start your review of Extract Records With Multiple Contains - Partial Text Criteria: 4 Examples

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.