How to Match Data from One Spreadsheet to Another

Trying to stitch together information from two different spreadsheets can feel like a headache waiting to happen. You have sales data in one tab and customer contact details in another, and you need to bring them together to see the full picture. This article will show you exactly how to match and pull data from one spreadsheet to another using the most practical functions in both Google Sheets and Microsoft Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why You Need to Match Data Across Spreadsheets

Before jumping into the formulas, let’s look at why this is such a common task. Businesses spread their data across multiple platforms, and spreadsheets often become the central hub for analysis. You might need to:

• Enrich Sales Data: Combine a report from your e-commerce platform (like Shopify) listing 'Order ID' and 'Product SKU' with your internal inventory sheet that contains 'Product Name' and 'Cost'.
• Build a Marketing List: Match a list of marketing-qualified leads (names and email addresses) from your CRM with an event registration list to see which leads attended a recent webinar.
• Update Customer Records: You have a master customer list and a newer, smaller spreadsheet with updated phone numbers for a handful of them. You need to pull those new numbers into your master list without manual copy-pasting.

In all these scenarios, the goal is the same: use a common piece of information in both spreadsheets to link them together and pull in additional data.

The Golden Rule: Find Your Unique Identifier

To successfully match data, your two spreadsheets must have at least one column in common. This shared column acts as a "key" that links a row in the first sheet to the corresponding row in the second.

This key is called a unique identifier. It’s a piece of data that is unique to each record. Good examples of a unique identifier include:

• Email Address
• Customer ID
• Order Number
• Product SKU
• Employee ID

Using something like "First Name" is a bad idea because you could easily have multiple people named "John." An email address, on the other hand, is almost always unique to one person. Before you start writing formulas, identify the unique identifier that exists in both of your datasheets.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Matching Data in Google Sheets with VLOOKUP

The 'VLOOKUP' function is the classic, go-to tool for this task in both Google Sheets and Excel. It stands for "Vertical Lookup" and it scans down the first column of a table to find a specific value, then returns a corresponding value from a different column in the same row.

Let's use a simple example. We have two sheets:

1. A 'Sales' sheet with Order Number, Customer Email, and Revenue.
2. A 'Customers' sheet with Customer Email and Company Name.

Our goal is to pull the Company Name into our 'Sales' sheet so we can see which companies are driving the most revenue. Our unique identifier here is the Customer Email.

Here's how to structure the 'VLOOKUP' formula:

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key: The value you're looking for. In our example, this is the Customer Email in the 'Sales' sheet (cell B2).
• range: The range of data to search within, including both your unique identifier column and the column with the data you want to retrieve. This would be columns A and B on our 'Customers' sheet. Crucially, the lookup value must be in the first column of this range.
• index: The column number in your range from which to return a value. The first column in the range is 1, the second is 2, and so on. We want the Company Name, which is the 2nd column in our selected range on the 'Customers' sheet.
• [is_sorted]: This should almost always be set to 'FALSE' (or '0'). This tells the function to look for an exact match. 'TRUE' is used for approximate matches and can cause confusing results if you're not careful.

Step-by-Step VLOOKUP Example

1. Go to the primary sheet ('Sales') and click on the first empty cell where you want the company name to appear (e.g., cell D2).
2. Type the formula:

=VLOOKUP(B2, 'Customers'!A:B, 2, FALSE)

Let’s break it down:

• B2 is the email address of the first customer in our 'Sales' sheet.
• 'Customers'!A:B tells Google Sheets to look at columns A through B in the 'Customers' sheet. This is our lookup table.
• 2 tells the formula to return the value from the second column of that table ('Company Name').
• FALSE ensures we only get a result if it finds an exact email match.

1. Hit Enter. The corresponding company name should appear. You can then click the small blue square (the fill handle) at the bottom-right of the cell and drag it down to apply the formula to all your sales data.

Pro Tip: When you drag the formula down, make sure to lock your lookup range using dollar signs (absolute reference), like this: 'Customers'!$A:$B. This prevents the range from shifting as you copy the formula down rows.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

A More Flexible Method: INDEX/MATCH

While 'VLOOKUP' is great, it has one major limitation: it can only look up values in the first column of your selected data range. If your unique identifier isn't in column A, you're out of luck. This is where the powerful duo of 'INDEX' and 'MATCH' comes in handy.

Combining these two functions is more flexible and robust. Here's a quick look at what they do:

• MATCH: Looks for a value in a range and returns its position (e.g., it tells you the email "hello@company.com" is in the 5th row of column A).
• INDEX: Returns the value of a cell at a specific row and column position inside a range.

When used together, you first use 'MATCH' to find the row number of your unique identifier, then feed that row number into 'INDEX' to pull the corresponding value from any other column you want.

The combined formula looks like this:

=INDEX(column_to_return, MATCH(lookup_value, column_to_lookup_in, 0))

Step-by-Step INDEX/MATCH Example

Using our same 'Sales' and 'Customers' example, here’s how to build the formula in cell D2 of the 'Sales' sheet:

=INDEX('Customers'!B:B, MATCH(B2, 'Customers'!A:A, 0))

Thinking from the inside out:

• MATCH(B2, 'Customers'!A:A, 0): Looks for the email in cell B2 within the entire column A of the 'Customers' sheet. The '0' specifies an exact match. It will return the row number where it finds the email.
• =INDEX('Customers'!B:B, ...): Takes the row number provided by the 'MATCH' function and returns the value from that same row in column B of the 'Customers' sheet (the 'Company Name').

The main benefit here is that your columns can be in any order. The email and company name columns could be switched on the 'Customers' sheet and this formula would still work perfectly, whereas a 'VLOOKUP' would break.

Matching Data in Excel: Meet XLOOKUP

Both 'VLOOKUP' and 'INDEX/MATCH' work exactly the same in Microsoft Excel. However, newer versions of Excel (Excel for Microsoft 365, Excel 2021) include a powerful and much simpler hero: 'XLOOKUP'.

'XLOOKUP' was designed to replace 'VLOOKUP' and fixes all of its annoying limitations in one-easy-to-use function.

Here’s the basic syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

• lookup_value: The value you’re searching for (e.g., our email in cell B2).
• lookup_array: The single column or row where you want to search for that value (e.g., column A in the 'Customers' sheet).
• return_array: The single column or row from which you want to return a value (e.g., column B in the 'Customers' sheet).
• [if_not_found]: An optional value to return if no match is found. Instead of showing an '#N/A' error, you can make it say "Not Found" or "Missing Info".

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step XLOOKUP Example

In our Excel 'Sales' sheet, click on cell D2 and type:

=XLOOKUP(B2, 'Customers'!A:A, 'Customers'!B:B, "No Company Found")

This formula tells Excel:

"Look for the email from cell B2 inside column A of the 'Customers' sheet. When you find it, give me the value from the same row in column B of the 'Customers' sheet. If you can't find a match, just display the text 'No Company Found'."

It’s clean, easy to read, and defaults to an exact match, making it the best option if you're working in a modern version of Excel.

Common Pitfalls and How to Fix Them

Often, your formula will return an '#N/A' error, which means it couldn't find a match. This is usually due to small inconsistencies in your data.

• Extra Spaces: A common culprit is a leading or trailing space in one cell but not its counterpart (e.g., "jane@doe.com" vs. "jane@doe.com"). Use the 'TRIM()' formula on your unique ID columns to clean this up before doing your lookup.
• Number vs. Text: Sometimes, an ID like "10025" is stored as a number in one sheet and text in another. Make sure the formatting is consistent across both sheets by selecting the column and choosing the correct format from the home tab.
• Incorrect Range: Double-check that your 'VLOOKUP' range is correct and that you're using absolute references (like '$A:$B') if you are dragging the formula down a list.

Final Thoughts

Learning how to connect spreadsheets with 'VLOOKUP', 'INDEX/MATCH', or 'XLOOKUP' is an incredibly valuable skill. It turns static lists into dynamic reports and saves you countless hours of manual copy-pasting. By finding a unique identifier and applying these functions, you can automate data enrichment and get a more complete picture of your business.

While mastering spreadsheet formulas is powerful, it's often the tedious first step in a much larger analysis. The process of exporting CSVs, cleaning data, and writing lookups is exactly the kind of manual work that drains your time. That’s why we built Graphed. Instead of manually matching data between sheets, you can connect your data sources (like Google Sheets, Shopify, or Google Analytics) directly, and watch your dashboards update in real-time. We help you skip the spreadsheet-wrangling and move straight to getting answers by letting you build reports and dashboards just by describing what you want in plain English. With Graphed , you can focus on insights, not formulas.