digest
Short Summary
Main recommendation: Use the FILTER dynamic array function in Office 365 as a lookup tool, because it returns multiple matching records for one or more lookup conditions without the complex workarounds that VLOOKUP or INDEX require. The video demonstrates three cases: a single lookup returning all matches, a two-condition AND lookup with an IF EMPTY fallback, and a flexible version that returns everything when a criteria cell is blank.
Key Points
- FILTER handles multiple return values for one lookup value (e.g., all sales for Fred Meyer or Amazon), which VLOOKUP and INDEX struggle with.
- Two conditions can be combined by multiplying two logical tests inside FILTER, so only records meeting both conditions are returned.
- The IF EMPTY argument supplies a fallback message such as "none" when no records match.
- Blank criteria cells can be handled with ISBLANK, so the formula shows all records when a condition is left empty.
- Dynamic arrays were limited to Office 365 Insider builds at the time of the video, with broader availability expected by early 2019.
Details Worth Noting
- Concrete instructions:
- Single lookup returning all matches: type =FILTER(revenue column, customer column = lookup cell), then press Enter.
- Two AND conditions with fallback: use =FILTER(table, (customer column = customer cell) * (sales rep column = rep cell), "none"). Multiplying the two logical tests yields 1 only when both are true.
- Show all records when criteria are blank: wrap each condition in IF(ISBLANK(criteria cell), SEQUENCE(ROWS(customer column), 1, 1, 0), condition test), so a blank cell produces a vertical array of ones that keeps every row.
- Copy the IF/SEQUENCE block for the second condition and multiply the two results together inside FILTER.
- Multi-condition with a numeric threshold: use =FILTER(date:diet columns, (player column = "Buddy") * (weight lifted > 250)) to pull only the matching columns and rows, returning nothing when no rows qualify.
- The narrator says FILTER can replace older lookup formulas for single and multiple conditions, showing it as an improvement over the old formulas he previously used for these tasks.
- He mentions that if you use a data validation drop-down and never let the cells go empty, the simpler AND-condition formula (without the ISBLANK/SEQUENCE trick) works fine.
Bottom Line
FILTER removes the need for complicated lookup formulas when a lookup must return several records or check more than one condition, and it is worth learning in Office 365 as a single, flexible tool.
