digest
Short Summary
Main recommendation: To check whether an item from one list appears in another, combine MATCH with ISNUMBER (for "is in the list") or with ISNA (for "is not in the list"). MATCH returns the item's position if found and #N/A if not, and the wrapper function converts that into TRUE or FALSE. The video also covers a bonus approach using VLOOKUP with NOT.
Key Points
- The video frames every two-list comparison around two questions: "Is this item in the list?" (TRUE if yes, FALSE if no) and "Is this item not in the list?" (TRUE if absent, FALSE if present).
- MATCH with exact match (the third argument set to 0) returns the item's relative position when found and #N/A when missing, so the error value is what signals absence.
- Wrapping MATCH in ISNUMBER produces TRUE/FALSE results for "is in list," which can then be sorted, filtered, or used in array formulas.
- Wrapping MATCH in ISNA produces TRUE/FALSE results for "not in list," as in the library example where it flags a book that has not been returned.
- A VLOOKUP-based bonus formula with a column index of 1 returns the matched text itself; wrapping that in ISERROR (with NOT for the "in both lists" check) gives the same comparison.
Details Worth Noting
- Concrete instructions:
- For "is item in list": enter =ISNUMBER(MATCH(item_cell, $list_range, 0)), with the lookup value as a relative reference and the list range locked with F4 so it stays fixed when copied down.
- For "is item not in list": enter =ISNA(MATCH(item_cell, $list_range, 0)) using the same MATCH structure.
- Use Control+Enter to place the formula in the cell while keeping the selection, then double-click the fill handle to copy it down the column.
- Once TRUE/FALSE results exist, sort, filter, or build array formulas from them.
- Bonus method: use VLOOKUP with column index 1 to return the matched text, then test that result with ISERROR, wrapping NOT around it to check whether it is in both lists.
- The example list comparison uses "Produce Fast and Fresh" (found in position 2) and "Healthy Garden" (not found, returning #N/A), showing the formula's TRUE/FALSE output in practice.
- The library inventory example uses a book found in position 83, and when copied down, "East of Eden" returned a #N/A error, illustrating a book not returned.
- The speaker notes that comparing two lists this way is the base for other formulas, and mentions a playlist of related list-merging videos and the next video on extracting items not in the second list.
Bottom Line
Pairing MATCH with ISNUMBER or ISNA gives a reliable TRUE/FALSE check for whether items from one list exist in another, and those results can then drive sorting, filtering, or further formulas.
