How to Match Data in Google Sheets

Trying to make sense of two different lists in Google Sheets and figure out how they connect is a common reporting headache. Whether you're matching product SKUs to inventory levels or email addresses to purchase history, this process is essential for building a complete picture. This guide will walk you through the best methods to match data in Google Sheets, from well-known classics to powerful modern functions that will save you hours of manual work.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Classic Approach: Using VLOOKUP to Find Matching Data

VLOOKUP (Vertical Lookup) is one of the most well-known functions for matching data in any spreadsheet. It's designed to search for a specific value in the first column of a table and return a corresponding value from a different column in the same row.

Imagine you have one sheet called "Sales" with a list of Order IDs and Product SKUs, and another sheet called "Products" with the Product SKU and its Price. Your goal is to pull the Price into your "Sales" sheet for each order.

Understanding the VLOOKUP Formula

The syntax for VLOOKUP looks like this:

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

• search_key: The value you're looking for (e.g., the specific product SKU in your "Sales" sheet).
• range: The range of cells that contains the data you're searching through (e.g., the table on your "Products" sheet). Crucially, the search_key must be in the very first column of this range.
• index: The number of the column in your range that contains the value you want to return. The first column is 1, the second is 2, and so on.
• [is_sorted]: This should almost always be FALSE or 0. This tells the formula to find an exact match for your search_key. If you omit it or use TRUE, it will look for an approximate match, which can lead to frustratingly incorrect results.

Step-by-Step Example

Let's use our Sales and Products example. Your "Sales" sheet looks like this:

• Column A: Order ID
• Column B: Product SKU
• Column C: Price (this is where our formula will go)

Your "Products" sheet has:

• Column A: Product SKU
• Column B: Price
• Column C: Inventory

1. Go to cell C2 in your "Sales" sheet.
2. Type the following formula:

=VLOOKUP(B2, Products!A2:C100, 2, FALSE)

Let’s break that down:

• B2 is our search_key - the SKU for the first order.
• Products!A2:C100 is our range, looking at columns A through C on the "Products" sheet. We use $ signs like Products!$A$2:$C$100 to lock the range if we plan to drag the formula down.
• 2 is our index because the Price is in the second column of our defined range.
• FALSE tells VLOOKUP to find an exact match for the SKU.

1. Press Enter, and Google Sheets will find the SKU from cell B2 in the "Products" sheet and return the price from the second column. You can then drag this formula down column C to find the price for every order.

VLOOKUP is reliable and widely used, but its major limitation is its inability to look "left" - the value you're searching for must always be in the first column of the specified range.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

A More Flexible Method: Combining INDEX and MATCH

For more flexibility, many power users prefer to combine two separate functions: INDEX and MATCH. While a bit more complex to set up initially, this combination overcomes VLOOKUP's biggest weakness and is more resilient if your spreadsheet structure changes.

• MATCH: Tells you the row number of a value within a specified range.
• INDEX: Returns the value of a cell at a specific row and column number.

Understanding the Formulas

First, let's look at them separately.

The MATCH syntax is: =MATCH(search_key, range, [search_type]). It finds the search_key (like a product SKU) inside the range (a single column) and returns its numeric position. We'll use 0 for search_type for an exact match.

The INDEX syntax is: =INDEX(reference, [row], [column]). It looks at a reference (a table or a column) and returns the value located at the specified row (and optional column).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Putting Them Together for a "Left Lookup"

When you use them together, MATCH finds the correct row for you, and you feed that directly into the row part of the INDEX function.

Let's adjust our earlier scenario. Now, your "Products" sheet is poorly organized, and the Product SKU is in Column B, while the Price is in Column A.

• Column A: Price
• Column B: Product SKU

VLOOKUP is useless here because it can't look left. But INDEX/MATCH can handle it easily.

1. In your "Sales" sheet, cell C2, enter this formula:

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

Let's review the parts:

• The MATCH(B2, Products!B:B, 0) part runs first. It looks for the SKU from cell B2 within Column B of the "Products" sheet and finds its row number. Let's say it finds it in row 15.
• The formula then simplifies to =INDEX(Products!A:A, 15).
• Finally, the INDEX function looks at Column A of the "Products" sheet and returns whatever value is in the 15th row - which is our price.

This method is more robust because it references entire columns instead of a fixed range. If you insert a new column between Price and SKU in your "Products" sheet, VLOOKUP would break because the index number would be wrong. INDEX/MATCH, however, would still work perfectly because it’s looking at the content of the columns themselves, not their position.

The Modern Solution: XLOOKUP (Your New Best Friend)

XLOOKUP is Google's modern answer to all data-matching challenges. It's simpler to write than INDEX/MATCH and more powerful than VLOOKUP. If you spend any time matching data in Sheets, learning XLOOKUP is well worth it.

Understanding the XLOOKUP Formula

The basic syntax is refreshingly straightforward:

=XLOOKUP(search_key, lookup_range, result_range, [if_not_found])

• search_key: The value you're looking for.
• lookup_range: The single column where you want to search for the search_key.
• result_range: The single column from which you want to pull the corresponding value.
• [if_not_found]: An optional, super-helpful argument. You can specify what to show if no match is found (e.g., "Not Found" or 0). This eliminates the need for messy IFERROR functions.

Unlike VLOOKUP, XLOOKUP defaults to an exact match, and the lookup and result columns can be anywhere you want - they don't even have to be next to each other!

Step-by-Step Example

Using the same "messy" "Products" sheet from our INDEX/MATCH example (Price in A, SKU in B):

1. In your "Sales" sheet, cell C2, your formula is:

=XLOOKUP(B2, Products!B:B, Products!A:A, "Product Not Found")

That's it. It's clean, easy to read, and solves all our previous problems:

• It looks for the SKU from B2.
• It searches for it in the lookup_range, Products!B:B.
• It returns the value from the same row in the result_range, Products!A:A.
• If it can’t find the SKU, it will say “Product Not Found” instead of showing a cryptic #N/A error.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Beyond Pulling Data: Comparing Two Lists for Matches

Sometimes you don't need to pull in related information - you just need to know if an item in one list exists in another. For example, you have an attendance list from a webinar, and you want to see which of those attendees are already in your master CRM contact list.

The COUNTIF function is perfect for this.

Step-by-Step Example

Let's say you have these two sheets:

• "Webinar Attendees": A single column (A) of email addresses.
• "CRM Contacts": A single column (A) with all your contact emails.

1. In the "Webinar Attendees" sheet, go to cell B2 (next to the first email address).
2. Enter this formula:

=COUNTIF('CRM Contacts'!A:A, A2)

Here's what it’s doing:

• 'CRM Contacts'!A:A is the range we're checking against.
• A2 is the specific email address we are looking for in that range.

1. Press enter and drag the formula down. The formula will return 1 if the email exists in your CRM list and 0 if it's a new contact. You can now easily filter Column B for all the 0s to get a clean list of new leads to add to your CRM.

Final Thoughts

Trying to connect the dots between different datasets is a universal task, but it doesn't have to be a drag. Whether you use the classic VLOOKUP, the flexible INDEX/MATCH, the superior XLOOKUP, or the simple COUNTIF for comparison, you now have a complete toolkit for matching data in Google Sheets and getting the full story from your numbers.

Manually matching data in spreadsheets, powerful as it is, often points to a larger challenge: your data is fragmented across different tools. Instead of constantly exporting CSVs and wrestling with formulas, we built Graphed to remove the busywork. By connecting directly to sources like your CRM, Shopify store, and ad platforms, we streamline the entire analysis process. You can just ask questions in plain English to get answers and build real-time dashboards that always stay up-to-date, skipping the spreadsheet VLOOKUP marathon entirely.