How to Look Up Data from Another Sheet in Excel

Trying to pull information from one Excel sheet into another is a task that brings many people's productivity to a screeching halt. You know the data you need is somewhere in your workbook, but manually copying and pasting sets you up for mistakes and wastes valuable time. This tutorial walks you through the three best methods for looking up data from another sheet in Excel: VLOOKUP, the modern XLOOKUP, and the powerful INDEX/MATCH combination.

Why Look Up Data Between Sheets?

Working across multiple sheets is a common and highly effective way to organize your data. Instead of cramming everything into one massive, unreadable sheet, you can separate your data logically. For instance, you might have one sheet for sales transactions, another for customer contact information, and a third for product details.

The challenge arises when you need to connect that information. Let's say you're building a sales report. Your Sales sheet might have the Customer ID and Product SKU for each transaction, but not the customer's name or the product's price. Those details live on your Customers and Products sheets. Looking up this data allows you to pull the customer's name and product price directly into your sales report, creating a single, comprehensive view without compromising your organized data structure.

This skill is useful in tons of scenarios:

• Consolidating sales data: Combine order details with customer and product information for complete reporting.
• Marketing analysis: Pull campaign cost data from one sheet into a lead generation report on another to calculate cost-per-lead.
• Inventory management: Look up product names, suppliers, and costs from a master Products sheet to update your Inventory sheet.
• HR and payroll: Fetch employee department and salary details from an Employees sheet for a payroll summary on a Payroll sheet.

The Classic Method: VLOOKUP

VLOOKUP (Vertical Lookup) is the most well-known lookup function in Excel. It's been the go-to solution for decades and works in virtually all versions of Excel, making it a reliable choice for compatibility. VLOOKUP searches for a value in the first column of a data range and returns a corresponding value from a specified column to the right.

Think of it like looking up a person's name in a phone book (the first column) to find their phone number (a column to the right).

Understanding the VLOOKUP Formula

The syntax for VLOOKUP can look intimidating at first, but it's straightforward once you break it down.

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

• lookup_value: The piece of information you already have. This is the common link between your two sheets, like a Product ID or an email address.
• table_array: The range of cells on the other sheet that contains the data. This range must start with the column containing your lookup_value.
• col_index_num: The column number within your table_array from which you want to pull data. The first column is 1, the second is 2, and so on.
• [range_lookup]: This optional part tells Excel whether you want an exact match (FALSE) or an approximate match (TRUE). In 99% of business cases, you will want an exact match, so you'll almost always use FALSE.

Step-by-Step Example

Let's imagine you have a workbook with two sheets. The first, named Sales, contains a list of orders. The second, named Products, is a master list of your products and their prices.

Your Goal: Pull the Price from the Products sheet into the Sales sheet for each order.

Products Sheet:

• Column A: Product ID (e.g., "SKU-001")
• Column B: Product Name (e.g., "Running Shoes")
• Column C: Price (e.g., "$99.99")

Sales Sheet:

• Column A: Order ID
• Column B: Product ID
• Column C: Price (This is where your formula will go)

Here’s how to do it:

1. Navigate to the Sales sheet and click on cell C2, the first cell where you want the price to appear.
2. Type =VLOOKUP( to start the formula.
3. For the lookup_value: Click on cell B2 in your Sales sheet. This is the unique Product ID for that order. Your formula now looks like =VLOOKUP(B2,.
4. For the table_array: Click over to your Products sheet. Select the entire range of your data, from the first Product ID to the last price. For example, A2:C100. Notice that Excel automatically adds the sheet name to the formula, which is crucial. It will look something like this: =VLOOKUP(B2, Products!A2:C100,. Pro Tip: Press F4 (or Fn + F4 on some laptops) to add dollar signs ($A$2:$C$100) to your range. This "locks" the range so it doesn't change when you drag the formula down.
5. For the col_index_num: You want to return the Price, which is the 3rd column in the selected range (A is 1, B is 2, C is 3). So, you'll type 3. The formula is now =VLOOKUP(B2, Products!A2:C100, 3,.
6. For the range_lookup: You need an exact match, so type FALSE and close the parenthesis.

Your final formula is:

=VLOOKUP(B2, Products!$A$2:$C$100, 3, FALSE)

Press Enter. Excel will find the Product ID from Sales (B2) in the first column of your Products range and return the price from the third column. You can then click the small square in the bottom-right corner of cell C2 and drag it down to apply the formula to all your orders.

Limitations of VLOOKUP

VLOOKUP is reliable but has a few key limitations:

• It can only look to the right. The lookup value must be in the first column of the table_array.
• If you insert or delete a column in your source data, your col_index_num will be wrong, and the formula will break or pull incorrect data.
• It can be slow on very large datasets.

The Modern & More Flexible Method: XLOOKUP

Available in Excel for Microsoft 365 and Excel 2021, XLOOKUP is the powerful and flexible successor to both VLOOKUP and HLOOKUP. It addresses every limitation of the older functions and is much easier to use.

The best part? XLOOKUP is more intuitive. Instead of defining a whole table and counting columns, you simply select your lookup column and your return column.

Understanding the XLOOKUP Formula

Let's look at the basic syntax:

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

• lookup_value: Same as VLOOKUP - the value you want to search for.
• lookup_array: The single column (or row) where you want to search for the lookup_value. This is a huge improvement over VLOOKUP's table_array.
• return_array: The single column (or row) from which you want to return a value.
• [if_not_found]: An optional, super useful argument. If the lookup value isn't found, you can tell XLOOKUP what to return (like "Not Found" or 0) instead of the default #N/A error.

Step-by-Step Example

Using the same Sales and Products sheets, here’s how to use XLOOKUP:

1. In cell C2 of your Sales sheet, type =XLOOKUP(.
2. For the lookup_value: Click on B2 in the Sales sheet: =XLOOKUP(B2,.
3. For the lookup_array: Switch to the Products sheet and select the entire column where the Product IDs are located (e.g., column A). The formula will read =XLOOKUP(B2,Products!A:A,.
4. For the return_array: While still on the Products sheet, select the entire column containing the prices you want to return (e.g., column C): =XLOOKUP(B2,Products!A:A,Products!C:C).

That's it! You can close the parenthesis and press Enter. Unlike VLOOKUP, XLOOKUP defaults to an exact match, so you don't even need to add FALSE.

Your final formula is beautifully simple:

=XLOOKUP(B2, Products!A:A, Products!C:C)

You'll get the same result as VLOOKUP, but the process is far more logical and less prone to error if you modify your table structure later.

The Power Combo: INDEX and MATCH

Before XLOOKUP existed, Excel power users relied on a combination of two functions: INDEX and MATCH. This pair accomplishes the same thing but with more flexibility than VLOOKUP and works in all versions of Excel. If you or your teammates are using older versions of Excel and need something more dynamic than VLOOKUP, this is the best option.

Here's how they work together:

• MATCH: Tells you the position (the row number) of a value within a range.
• INDEX: Returns the value at a given position within a range.

By nesting MATCH inside of INDEX, you can create a super-powered lookup. You tell MATCH to find the row number of your lookup value, and then you use that number to tell INDEX which value to retrieve from another column.

Understanding the INDEX and MATCH Formula

The combined formula looks like this:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

• return_array: The column from which you want to pull data (similar to XLOOKUP's return_array).
• lookup_value: The value you're looking for.
• lookup_array: The column where you'll search for the lookup_value.
• 0: This tells the MATCH function you want an exact match.

Step-by-Step Example

One last time, let's use our Sales and Products sheets:

1. Click on cell C2 in your Sales sheet and type =INDEX(.
2. For the return_array of INDEX: Switch to the Products sheet and select the Price column (e.g., Column C). Your formula is now =INDEX(Products!C:C,.
3. For the row number of INDEX: Now, you need to open your MATCH function where the second argument of INDEX goes. Type MATCH(. Excel will now ask for the MATCH arguments.
4. For the lookup_value of MATCH: Switch back to the Sales sheet and click on cell B2: =MATCH(B2,.
5. For the lookup_array of MATCH: Go to the Products sheet again and select the column containing the Product IDs (Column A): MATCH(B2, Products!A:A,.
6. For the match type: Type 0 for exact match.
7. Close both functions with two parentheses: )).

The complete formula looks like this:

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

Press Enter, and it works perfectly. Like XLOOKUP, it’s not dependent on column order and won’t break if you insert or delete columns.

Which Method Should You Use?

With three reliable options, it can be tricky to know which one to pick. Here's a quick guide:

• Use XLOOKUP if you can. If you and anyone you share your report with have a modern version of Excel (Microsoft 365 or 2021+), XLOOKUP is the clear winner. It's the most flexible, intuitive, and easiest to use.
• Use INDEX/MATCH for backward compatibility and flexibility. If you need your spreadsheet to work on older Excel versions and VLOOKUP's limitations won't cut it, INDEX/MATCH is your best bet. It's the classic go-to for advanced users.
• Use VLOOKUP for simple tasks and maximum compatibility. If you have a straightforward, right-looking lookup and need to ensure your file works for everyone, regardless of their Excel version, VLOOKUP is a serviceable choice. Just be aware of its limitations.

Final Thoughts

Knowing how to connect and look up data between different sheets is an essential Excel skill that streamlines your reporting and analysis. Whether you choose the trusty VLOOKUP, the powerful INDEX/MATCH duo, or the superior XLOOKUP, you'll be able to create richer, more insightful reports without the headaches of manual data entry.

Mastering spreadsheet lookups is a huge step, but it often reveals a bigger challenge: your data is scattered across tools that don't speak to each other. We built Graphed to solve this problem entirely. Instead of wrestling with formulas to connect data from different sources like Shopify, Google Analytics, or Facebook Ads, you can link them all in one place. Then, you just ask questions in plain English, and Graphed instantly builds the real-time dashboards and reports you need, saving you from the hours typically spent pulling data and building reports by hand.