VLOOKUP() from Another Sheet: A How-to in Excel

Have you ever spent hours manually searching through spreadsheets to find matching data? I've been there, and it's frustrating. That's why I'll show you how VLOOKUP() changed that way I work with Excel — and how it can do the same for you.

In this guide, you will understand how to use VLOOKUP() to search and retrieve data, whether in different sheets of your workbook or even in entirely separate Excel files. However, if you're not familiar with Excel functions at all, start with our Introduction to Excel course to get familiar with the basics.

The Quick Answer: How to Use VLOOKUP() from Another Sheet

Here's the fastest way to use VLOOKUP() to find data in another sheet:

=VLOOKUP(lookup_value, Sheet2!range, col_index, [range_lookup])

The key is in the second argument, where you reference the range in another sheet. You have to add the sheet name followed by an exclamation mark before your range, like this: SheetName!A2:D100

For example, if you're looking up a customer ID in the Customers sheet, you would use this syntax:

=VLOOKUP(A2, Customers!A2:D100, 2, FALSE)

And if your sheet name contains spaces, simply wrap it in single quotes like this:

=VLOOKUP(A2, 'Customer Data'!A2:D100, 2, FALSE)

Why We Use VLOOKUP() from Another Sheet 

VLOOKUP(), as you may know, lets you search for a value in a table and pull back a related value from a specified column. I say column because the ‘V’ in VLOOKUP() means ‘vertical’. VLOOKUP() is great because it mean, when you update information in one place, the information in another place is also updated. That is, updates happen automatically everywhere they're referenced.

Now, I use VLOOKUP() across different sheets because I store different types of information in different places. This sounds obvious but has an implication: It gives me a specific sheet that I could use for manual data entry, and it also, therefore, eliminates errors that can creep in. By knowing how to use VLOOKUP() from another sheet, I am better able to maintain clean datasets without redundant details. I can work faster, and I make fewer mistakes.

A Closer Look at VLOOKUP() from Another Sheet

The first step in using VLOOKUP() across sheets is figuring out what information you'll use to connect the data. We call this the lookup value or common field. It's the data that exists in both sheets and helps Excel match things up, and you must format it consistently in both sheets. Minor formatting differences, like hidden spaces or inconsistent data types, can give an #N/A error.

So, before you start writing your VLOOKUP() formula, make sure the lookup value exists in both sheets, the data types, like text or numbers, are consistent, and there are no hidden spaces or trailing characters.

Breaking down the VLOOKUP() formula

When writing a VLOOKUP() that pulls data from another sheet, the main adjustment is in the table_array parameter. You must add the sheet name followed by an exclamation mark (!) before your range. 

Let's say you have two sheets: Employee Data (where the data is stored) and Salary Data (where you want to display the salaries of each employee based on lookup value).

Employee Data sheet. Image by Author

Salary Data sheet. Image by Author

To transfer the salaries from the Employee Data sheet to the Salary Data sheet:

1. Select the cell where you want the result.

2. Start the VLOOKUP() formula: =VLOOKUP(

3. Select lookup value: =VLOOKUP(A2,

4. Reference the other sheet (this is the key part): =VLOOKUP(A2, Employee Data!A2:D4,

5. Complete the formula (4 for specifying column, FALSE for exact match): =VLOOKUP(A2, 'Employee Data'!A2:D4, 4, FALSE)

When the above formula is applied, you'll get the following results:

I would say that the most common mistake in a cross-sheet VLOOKUP() is incorrect sheet name references. So double-check this part of your formula if you ever face any issues in performing a cross-sheet VLOOKUP().

Example VLOOKUP() from another sheet

Let's see another example. I have two sheets — Flavors and Total Sale. In the Total Sale sheet, I want to record the total sales of all the ice cream flavors for a month.

The Flavors sheet looks like this:

Flavors sheet. Image by Author

The Total Sale sheet looks like this:

Total Sale sheet. Image by Author

To get the total sales of the ice cream flavors from the Flavors sheet, I apply the following formula in cell B2 of the Total Sale sheet:

=VLOOKUP(A2,Flavors!A:E,5,FALSE)

This formula looks at the value in A2, searches for it in the Flavors sheet, and returns the result from the fifth column when it finds a match.

Total sales from one sheet to another. Image by Author

Troubleshooting Common Errors

Although it’d be easy to perform VLOOKUP() once you clearly understand how it works, you may encounter errors. Let’s see what are they and how to handle them:

#N/A errors

The dreaded #N/A error in VLOOKUP() can be frustrating, but it's usually fixable once you understand the cause. Here's how to spot and resolve:

• Mismatched Data Types: Sometimes, your lookup value may be stored as text in one sheet and as a number in another. Excel sees these as different, even if they look the same to you.

• Incorrect References: You may be looking in the wrong place – either the wrong sheet or the wrong range of cells. Double-check your sheet names and cell ranges. Make sure you've spelled the sheet name correctly and that your range includes all necessary data.

• Exact Match Issues: If you're using FALSE for an exact match (usually recommended), your lookup value must match exactly what's in your table. So make sure there are no hidden spaces — you can even use the TRIM() function to remove them.

• Lookup Value Not in the First Column: VLOOKUP() always looks for the lookup value in the first column of your range. So, make sure your lookup column is the leftmost column in your range.

• Broken References: If the sheet you're referencing is renamed, deleted, or moved, formulas will break and give errors. To avoid this, keep your referenced sheets intact.

Handling issues with cross-sheet references

Check for consistent formatting before looking up data between sheets. To do so, you can use Excel's TEXT() or VALUE() functions. For example, you can use the TEXT() function within VLOOKUP() like this:

=VLOOKUP(TEXT(A2,"0"), Sheet2!B2:D10, 3, FALSE)

This will indicate to Excel that your lookup value should be treated as text, which can help if your other sheet stores things as text, too.

Otherwise, instead of showing #N/A errors, you can provide more user-friendly messages using the IF() formula. Here’s how:

For basic error handling: 

=IFERROR(VLOOKUP(A2, Sheet2!B:C, 2, FALSE), "Not Found")

For more detailed error handling: 

=IFERROR(VLOOKUP(A2, Sheet2!B:C, 2, FALSE), 
  "No match found for " & A2)

For nested error handling with different messages: 

=IF(ISBLANK(A2),"Please enter a value", IFERROR(VLOOKUP(A2, Sheet2!B:C, 2, FALSE),
"No match found"))

For more details on how to combine VLOOKUP() and IF(), read my detailed tutorial, How to Combine VLOOKUP() with IF() in Excel.

Final Thoughts

VLOOKUP() across sheets is one of Excel's most powerful features for anyone working with multiple datasets. Once you master the simple trick of adding the sheet name to your formula, you can pull data from anywhere in your workbook. While the errors may look frustrating at first, most VLOOKUP() issues come down to simple data mismatches that are easy to fix once you know what to look for.

The best way to get comfortable with a cross-sheet VLOOKUP() is to practice with your own data. Start with simple lookups between two sheets, then gradually add error handling as you build confidence. For more advanced Excel techniques, DataCamp offers a variety of Excel tutorials to help you advance your skills. Also, our Excel Fundamentals skill track always looks good on a resume.

Finally, if you are looking to understand more of the nuance around VLOOKUP(), check out my other VLOOKUP() tutorials: