Cross-reference two lists with FILTER
IntermediateList reconciliation is a common operations task. Teams use it to spot catalog items that never sold, order records tied to missing master data, and other exceptions that need review before inventory or reporting decisions are made.
In this exercise, you'll practice using FILTER to return only the records that fail a match test against another list. This is a useful dynamic-array pattern when you need exception reports instead of full tables.
Compare the product catalog in A:B with the order history in D:E and identify the items that appear in one list but not the other.
Enter one spill formula in A16 that returns the catalog records with no matching order, including both the product code and product name. Enter a second spill formula in D16 that returns any ordered product codes that do not appear in the catalog. You will need to decide which range each formula should return and how to build the include test so it lines up with that returned array.
Need some help?
Hint 1
Each spill needs an include array with one TRUE or FALSE result per returned row. Start by thinking about which list should be checked for missing matches in the other list.
Hint 2
COUNTIF or MATCH can both build the missing-item test. For the result in A16, the logical test has to align with the catalog rows; for the result in D16, it has to align with the ordered product-code rows.
Cross-reference two lists with FILTER
IntermediateList reconciliation is a common operations task. Teams use it to spot catalog items that never sold, order records tied to missing master data, and other exceptions that need review before inventory or reporting decisions are made.
In this exercise, you'll practice using FILTER to return only the records that fail a match test against another list. This is a useful dynamic-array pattern when you need exception reports instead of full tables.
Compare the product catalog in A:B with the order history in D:E and identify the items that appear in one list but not the other.
Enter one spill formula in A16 that returns the catalog records with no matching order, including both the product code and product name. Enter a second spill formula in D16 that returns any ordered product codes that do not appear in the catalog. You will need to decide which range each formula should return and how to build the include test so it lines up with that returned array.
Need some help?
Hint 1
Each spill needs an include array with one TRUE or FALSE result per returned row. Start by thinking about which list should be checked for missing matches in the other list.
Hint 2
COUNTIF or MATCH can both build the missing-item test. For the result in A16, the logical test has to align with the catalog rows; for the result in D16, it has to align with the ordered product-code rows.