Lookup Function

A lookup function finds a value in one table and returns a related value from the same row, which is how a spreadsheet attaches information held somewhere else. VLOOKUP is the familiar one: give it the value to find, the range to search, which column of that range to return, and whether the match must be exact.

Two structural rules govern it. The key must be in the first column of the searched range — Google states that “the search key must be in the first column of the range” — and the returned column must lie to its right, since Microsoft notes VLOOKUP “can only look from left-to-right”. A table whose identifier sits in the middle therefore cannot be searched directly, which is a property of the function rather than a fault in the data.

Two arguments are where it breaks in practice. The searched range is an ordinary reference, so copying the formula down shifts it unless it is locked with dollar signs, producing results that are right at the top of a column and wrong below. And the return column is a counted position, so inserting a column inside the table leaves the number pointing at different data without raising an error. Microsoft documents the related failure: “if col_index_num is greater than the number of columns in table-array, you’ll get the #REF! error value.”

Both limitations have answers. INDEX and MATCH names the lookup column and the return column instead of counting, which survives inserted columns and can return values to the left. XLOOKUP, where available, folds the same improvements into one function with exact matching as its default and an if_not_found value in place of an error. Whichever you use, the function only compares what it is given: a lookup cannot rescue a key that is the wrong type, padded with spaces, or duplicated.

References:

• VLOOKUP function
• Google Sheets VLOOKUP
• Microsoft Support, XLOOKUP function