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

Learn advanced Excel techniques for extracting records with multiple contains criteria in this comprehensive 38-minute tutorial. Explore various methods including filter and helper columns with array formulas, advanced filter with formula criteria, and complex array formulas for counting and extracting matching records. Master the use of functions such as OR, ISNUMBER, SEARCH, LOOKUP, MMULT, TRANSPOSE, and more. Gain insights into matrix multiplication and the MMULT array function. Practice with provided start and finish files, and understand how to test array formulas when source data changes. Perfect for Excel users looking to enhance their data manipulation skills.

) 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..