How to Find Data from One Excel Sheet in Another

Manually copying and pasting data between Excel sheets is not only a drain on your time, but it’s also a surefire way to introduce errors into your work. Fortunately, there's a much smarter way to work by linking your sheets together. This guide will walk you through the most effective methods to automatically find and pull data from one sheet into another, starting with the basics and moving to more powerful functions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Connect Data Between Excel Sheets?

Before diving into the formulas, it’s helpful to understand why this is such a valuable skill. When you connect your data, you’re creating a dynamic and reliable system instead of a static, fragile one. Here’s what you gain:

• It saves time: Stop the endless cycle of finding, copying, and pasting. Once you set up a formula, it pulls the information for you instantly.
• It reduces errors: Every time you manually copy data, you risk making a mistake. Formulas eliminate human error, ensuring your data is accurate.
• It creates a "single source of truth": Imagine you have one sheet with customer contact information and another with their recent orders. Instead of having to enter an email address twice, you can have a single master list and simply pull the email address wherever it’s needed. If you update the email in the master list, it automatically updates everywhere.
• It simplifies updates: When your source data changes (e.g., a product price is updated), the sheet where you pulled the data will update automatically. No need to hunt down every instance and change it by hand.

For example, you could have a sheet of sales transactions that only includes a customer ID, and a separate sheet with an exhaustive list of customer details (name, email, location). Instead of manually adding the customer's email to every single sales transaction, you can use a formula to automatically look up the customer ID in your details list and pull in the corresponding email address.

The Classic Approach: Using VLOOKUP

For years, VLOOKUP (Vertical Lookup) has been the go-to function for finding data in one table and bringing it into another. It works by searching for a specific value in the first column of a data range and returning a corresponding value from a different column in that same range.

While newer functions have improved upon it, VLOOKUP is still incredibly common and useful. It's essential to understand how it works.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

The VLOOKUP Formula Explained

The syntax for the VLOOKUP function looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

This might look intimidating, but it’s straightforward once you break it down:

• lookup_value: What are you looking for? This is the unique piece of data that exists in both of your sheets (e.g., a "Product ID," "Order Number," or "Email Address").
• table_array: Where are you looking? This is the data range in your other sheet where you’ll search for the lookup_value. Crucially, the column containing your lookup_value must be the very first column in this range.
• col_index_num: What data do you want to get back? This is the column number (from your table_array) that contains the information you want to return. Count from left to right, with the first column being 1.
• [range_lookup]: Do you need an exact match? This is almost always set to FALSE, which tells Excel you need an exact match for your lookup_value. Leaving it blank or setting it to TRUE will look for an approximate match, which can lead to incorrect results if your data isn't sorted in a specific way.

Step-by-Step VLOOKUP Example

Let's use a practical example. Imagine we have two sheets:

1. A sheet named "Sales Records" with a list of transactions, including Product ID but missing Product Name and Price.
2. A sheet named "Product List" that serves as our master list of products, containing Product ID, Product Name, and Price.

Our goal is to pull the Product Name from the "Product List" sheet into the "Sales Records" sheet.

Step 1: Start your formula in the "Sales Records" sheet.

In the "Sales Records" sheet, click on the cell where you want the first product name to appear (let's say it's cell B2). Type =VLOOKUP( to begin.

Step 2: Set your lookup_value.

The common link between our sheets is the Product ID. Our first Product ID is in cell A2 of the "Sales Records" sheet. So, our formula starts as: =VLOOKUP(A2

Step 3: Define your table_array.

Now, we need to tell Excel where to look for the information. Go to your "Product List" sheet and select the entire range of data that contains both the Product IDs and the Product Names. For example, if your data is in columns A through C, from row 2 to row 50, your selection will be 'Product List'!A2:C50. It's best to make this an absolute reference by adding dollar signs ($) so the range doesn't shift when you drag the formula down. You can do this by highlighting the range and pressing F4. The formula becomes: =VLOOKUP(A2, 'Product List'!$A$2:$C$50

Step 4: Set the col_index_num.

We want to return the Product Name. In our selected table_array on the "Product List" sheet, the Product IDs are in column 1 and the Product Names are in column 2. So, we'll use 2 as our column index number. Our formula is now: =VLOOKUP(A2, 'Product List'!$A$2:$C$50, 2

Step 5: Demand an exact match.

We only want the formula to return a name if it finds an exact match for the Product ID. To do this, we add FALSE as the final argument. Our formula is complete:

=VLOOKUP(A2, 'Product List'!$A$2:$C$50, 2, FALSE)

Press Enter, and the correct product name will appear in the cell. You can then click the small square in the bottom-right corner of the cell and drag it down to apply the formula to all your sales records.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

The More Flexible Method: INDEX MATCH

VLOOKUP is great, but it has some limitations. Its biggest weakness is that it can only look up values in the leftmost column of your data range. This is where INDEX MATCH comes in. It's a powerful combination of two different functions that, when used together, is more flexible and resilient than VLOOKUP.

How INDEX and MATCH Work Together

Instead of one function doing everything, you use two:

• MATCH: This function finds the position (the row number) of a value in a specified range.
• INDEX: This function returns a value from a specific row and column in a given range.

To pull the Product Name using our previous example, the combined INDEX MATCH formula would look like this:

=INDEX('Product List'!$B$2:$B$50, MATCH(A2, 'Product List'!$A$2:$A$50, 0))

Here’s the step-by-step breakdown:

1. =MATCH(A2, 'Product List'!$A$2:$A$50, 0): This part goes first. It looks for the product ID from cell A2 of our sales sheet inside the product ID column of our product list (Column A). The 0 ensures an exact match. Let's say it finds it in the 5th row of that range, the MATCH function will return the number 5.
2. =INDEX('Product List'!$B$2:$B$50, 5): The INDEX function takes that '5' and goes to the "Product List" sheet. It looks at the range where our product names are kept (Column B) and returns the value from the 5th cell in that range.

The primary advantage of INDEX MATCH is that the product name and product ID columns can be anywhere in your sheet. Your lookup column doesn't need to be on the left.

The Easiest & Best Way: XLOOKUP (Excel 365+)

If you have a modern version of Excel (Microsoft 365 or Excel 2021), XLOOKUP is your new best friend. It was designed to replace both VLOOKUP and INDEX MATCH by taking the best parts of both and making them much easier to use.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

The Simple XLOOKUP Formula

The syntax for XLOOKUP is incredibly intuitive:

=XLOOKUP(lookup_value, lookup_array, return_array)

• lookup_value: What you're looking for (e.g., cell A2).
• lookup_array: The column where to look for it (e.g., the Product ID column in the other sheet).
• return_array: The column containing the data you want to get back (e.g., the Product Name column).

To solve our same example with XLOOKUP, the formula would just be:

=XLOOKUP(A2, 'Product List'!A:A, 'Product List'!B:B)

That's it! It’s clean, easy to read, and you don’t have to worry about column numbers or complex nesting. It also has built-in features that make it even more powerful, like a default for an exact match and an optional argument for what to show if a value isn't found, eliminating ugly #N/A errors.

Common Mistakes and How to Fix Them

When linking data, you may run into a few common errors. Here's what they mean and how to fix them:

• #N/A Error: This means "Not Available." Your formula couldn't find the lookup value. The most common causes are a typo in your data, extra spaces (use the =TRIM() function to clean up your data), or a number that's accidentally formatted as text.
• #REF! Error: This "Reference" error often happens with VLOOKUP. If your col_index_num is, say, 3, but you delete a column from your table array, the formula breaks because it doesn't know where column 3 is anymore. This is one of the main reasons INDEX MATCH and XLOOKUP are more robust.
• Getting an Incorrect Result: 99% of the time, this is because the [range_lookup] argument in VLOOKUP was set to TRUE or was left blank. This allows an approximate match, which can pull the wrong data. Always specify FALSE for an exact match.

Final Thoughts

Learning how to find data from one sheet and display it in another automates one of the most tedious manual tasks in Excel. Whether you choose the classic VLOOKUP, the flexible INDEX MATCH, or the modern XLOOKUP, you're now equipped to build more efficient, accurate, and dynamic spreadsheets.

As you get comfortable pulling data between sheets, you'll find that the next challenge is pulling data from totally different applications, like Google Analytics, Shopify, or your CRM. At Graphed, we automate that entire connection process. We make it easy to link your business data sources and then use simple natural language - not complex formulas - to create the dashboards and reports you need in seconds, keeping everything refreshed in real-time.