Excel Magic Trick 1382

Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples

DESCRIPTION

See how to:
(00:15) Introduction to Contains Criteria (Partial Text Criteria) in an OR Logical Test, when search for records with Multiple Contains Criteria.
(02:00) #1 Filter & Helper Column with Formula that uses OR, ISNUMBER & SEARCH functions. This Array Formula requires Ctrl + Shift + Enter.
(07:23) #2 Filter & Helper Column with Formula that uses ISNUMBER, LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter.
(12:11) #3 Easiest solution: Advanced Filter with Formula Criteria, where formula uses LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter.
(15:56) #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.
(21:45) 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.
(30:18) #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.
(36:52) Test Array Formulas when source data changes.
(37:39) Summary.

WORKBOOKS

Download “Excel Magic Trick 1381-1385” Excel-Magic-Trick-1381-1385.zip – Downloaded 75 times – 81 MB







Be the first to comment

Leave a Reply