Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples

Описание: Download Files:

Start File: https://excelisfun.net/files/EMT1382S...

Finish File: https://excelisfun.net/files/EMT1382F...

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

Match Job Title to List of Key Words

Reference Video: Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria