digest
Short Summary
Main recommendation: To filter an Excel table by a list of values (rather than one condition at a time), use the XMATCH function to compare the whole table column against the list, wrap that result in ISNUMBER to get TRUE/FALSE values, and feed those into the FILTER function. The video shows that this replaces a large older array formula with a much simpler one.
Key Points
- The problem being solved: filtering a table so that records match any value in a separate list, with results displayed off to the side, without applying each condition individually.
- XMATCH is the function to use when comparing one list against another; because the entire column is passed as the lookup_value, it returns one result per table row.
- XMATCH returns #N/A for rows whose category is not in the list and a position number for rows that match, so the two outcomes separate cleanly.
- ISNUMBER converts those results into TRUE (match, keep the row) and FALSE (no match, drop the row), which is the include argument FILTER needs.
- The speaker contrasts this with the older approach, which he describes as a large, complex array formula.
Details Worth Noting
- Concrete instructions:
- Make sure your data is an Excel table. Hover at the top of the column you want to reference until the black downward arrow appears and click to insert the structured reference (for example, ET[Expense Category]).
- In the XMATCH formula, set lookup_value to the entire table column (ET[Expense Category]) rather than a single cell.
- Set lookup_array to the list of values you want to match against (the list of categories).
- Leave XMATCH's optional arguments at their defaults, since the default is exact match, which is what is needed.
- Wrap the XMATCH formula in ISNUMBER, so the top cell returns TRUE for matching rows and FALSE for non-matching rows.
- In the FILTER formula, set the array argument to the whole table (click the diagonal arrow at the upper-left corner to select the entire table), and set the include argument to the TRUE/FALSE array from the ISNUMBER step.
- Press Enter; the result shows only the records whose categories appear in your list.
- The speaker notes that the workbook is downloadable from the video's description, which contains the example.
- The video also points to other FILTER videos through links, which were not named in the transcript.
- The method applies when you are comparing two lists; the speaker presents this as the key pattern to recognize.
Bottom Line
Once you see the task as comparing two lists, XMATCH plus ISNUMBER inside FILTER turns a complicated multi-condition filter into a short, reusable formula.
