Lookup Key

The lookup key is the value two tables have in common — an employee number, a product code, an email address — and every lookup depends on the two sides being the same value rather than merely looking the same on screen. It plays the role a primary and foreign key pair plays in a database, without anything enforcing that it is unique or well formed.

Three defects break matching, and all three are invisible. Types differ: 101 entered as a number and “101” imported as text are different values, and Microsoft lists this among the documented causes of a failed lookup — “the lookup value and the source data are different data types”. Spaces intrude: a trailing space makes a key unequal to its twin, and the documented fix is that “you can use the TRIM function to remove any leading or trailing spaces”. Duplicates exist: the lookup then answers instead of failing.

Uniqueness deserves separate attention because it fails quietly. Google documents that “VLOOKUP only returns the first match. If you have multiple matched search keys, a value is returned, but it may not be the expected value.” A key column with two rows for one entity therefore produces a plausible number chosen by row order — a one-to-many relationship that a lookup cannot express and will not report.

So the work happens before the formula. Confirm both sides hold the same type, trim both, and count the key column’s distinct values against its row count. Where two systems identify the same entity differently, no cleaning resolves it — that is a mapping decision about which identifier wins, and it belongs in writing next to the data rather than inside a formula.

References:

• How to correct a #N/A error in VLOOKUP
• Google Sheets VLOOKUP