Amey-Thakur Lookup functions
Lookups are where most spreadsheet data errors originate: a silent mismatch, a duplicate returning the first row, or an approximate match nobody intended. The defence is being explicit about all three.
Method
- Use the modern lookup function. Column-index-based lookups break when columns are inserted, which is the classic fragile spreadsheet failure.
- Always specify exact matching. Approximate match is the default in older functions and silently returns the nearest row, which is rarely intended.
- Handle the not-found case deliberately. A stated default or a visible marker, since an unexplained error propagates through every downstream calculation.
- Check for duplicate keys before trusting results. A lookup returns one match; if the key repeats, you are silently choosing one (see deduplication-queries).
- Clean keys before matching. Trailing spaces, inconsistent case, and text-formatted numbers are the usual causes of a lookup that should match and does not (see spreadsheet-data-cleaning).
- Count matches to validate the join. Comparing row counts before and after catches both dropped and duplicated rows (see sql-joins).
- Consider Power Query for repeated joins. A refreshable query beats a sheet of lookup formulas for anything done regularly (see power-query).
Boundaries
Lookups join on a single key and become unwieldy for multi-column matching. Large lookup ranges slow recalculation noticeably. Data that needs joining regularly probably belongs in a database rather than a spreadsheet.