Using VLOOKUP to Combine Data Across Spreadsheets#

📊 Analyze Data 🔗 Problem-Solving & Combining Data Lesson 015

Important

✨ AI-generated content. This page was written with the assistance of an AI language model and is provided as a learning aid. Despite careful review, it may still contain mistakes, omissions, or out-of-date information. Whether you are new to the topic, a team lead, or a senior practitioner, treat it as a starting point rather than an authoritative reference: read it critically and independently verify anything you act on (code, commands, figures, and factual claims) against official documentation and primary sources before relying on it.

Performing the lookup#

With the data prepared — clean keys, a leftmost lookup column, an adequate range — VLOOKUP performs the actual combination: pulling matching values from one table into another. This lesson covers the formula itself, its four arguments, and how it brings separate tables together by a shared key.

The VLOOKUP formula#

VLOOKUP takes four arguments:

=VLOOKUP(lookup_value, table_range, column_index, FALSE)

lookup_value — the value to search for (the key), e.g. this row’s product code.
table_range — the table to search, whose first column holds the keys and whose other columns hold the values to return.
column_index — which column of that range to return, counted from the left (1 is the key column itself, 2 the next, and so on).
the match type — FALSE (or 0) for an exact match, TRUE for an approximate match. Almost always use ``FALSE``: exact match is what combining data by key requires; TRUE (approximate) is for a different, rarer purpose and is a common source of silent wrong results.

=VLOOKUP(A2, products!A:C, 2, FALSE)

This reads: take the key in A2, search the first column of products!A:C, and return the value from the second column of that range (an exact match). Fill it down the column, and every row pulls its matching product detail from the products sheet.

Combining across sheets#

The power is combining separate tables. An orders sheet and a products sheet, linked by product code, become one enriched view: each order row gains its product’s name and price via VLOOKUP, without manually copying anything. This is exactly the relational combine — matching on a key to bring related data together — performed in a spreadsheet, and it is the everyday tool for the “combine data from two sources” task the cleaning section described.

VLOOKUP and the always-FALSE rule#

The single most important habit: use ``FALSE`` (exact match) unless you have a specific reason not to. Approximate match (TRUE) assumes the lookup column is sorted and returns the closest value at or below the lookup value — which for combining data by key is almost never what you want, and produces plausible wrong matches with no error. New users who omit the argument or use TRUE get mysterious wrong results; FALSE is the safe default for combining data, and should be your reflex.

The caveat#

VLOOKUP returns the first match and only pulls one column per formula, which has consequences: if the key is not unique in the lookup table, you silently get only the first matching row’s value (a data problem the preparation lesson flagged); and returning several columns needs several VLOOKUPs (or a different function). VLOOKUP also breaks if columns in the lookup range are inserted or deleted, since the column_index is a fixed number — a fragility that INDEX/MATCH and XLOOKUP avoid. And a returned value that looks right can still be wrong if the keys did not truly match as intended, so verify a sample of lookups against the source. The next lesson turns to diagnosing exactly these VLOOKUP failures.

Hint