How To Use Vlookup in Excel From Another Sheet
Microsoft Excel is easily one of the most widely-used tools today. Whether it’s a large enterprise using it to crunch data from thousands of sources across a hundred locations or a small company using it for everything from bookkeeping to tracking employee hours. But just entering data into your spreadsheets is one thing; you also need to know how to manipulate it. And sometimes, that means grabbing data from one sheet and displaying it in another. That’s where using VLOOKUP to pull Excel data from another sheet comes in.
VLOOKUP — which stands for Vertical Lookup — is an Excel formula that lets you search for a specific value in a table and return it in another cell of the same table. But you can also use it across spreadsheets. Here’s how.
VLOOKUP from another sheet step 1: Identify the common field
Before you start typing in any formulas, you need to know what you’re looking up. Look through both sheets and identify the common field that you’ll need to plug into your formula. For example, you might need to look up an employee’s phone number in your employee database to add it to a spreadsheet of your company’s softball team. Or you need to pull up the SKU for a number of products to create a database of your next store-wide promotion. Whatever the case, before you use VLOOKUP to pull data from another sheet, you need to make sure that this common field is formatted in exactly the same way in both sheets. Otherwise, you might run into some errors.
Step 2: Select the range of data
Once you’ve found the common field you need to look up, you have to determine where that value is located in the first spreadsheet. You can either take down the data range manually or highlight it with your cursor. That way, the VLOOKUP function will know where to look.
VLOOKUP from another sheet step 3: Enter the VLOOKUP function
Go to the cell where you want to display the result and start by typing =VLOOKUP(. Then specify the lookup value, the range of data, and the column number that you want to retrieve the value from. Here’s an example of a formula to use VLOOKUP with a different Excel sheet:
=VLOOKUP(B2,'[file name.xlsx]Sheet1′!A$2:B$249,2,false)
- B2 refers to the common field within the table where you’re running the formula.
- File name refers to the file name for the sheet you’re looking up data from.
- Sheet name is the name of the sheet you’re pulling data from.
- A$2:B$249 is the range of data from the source sheet.
- 2 is the indicator of the column which the formula should return (with the leftmost column of the range counting as 1)
- As VLOOKUP is usually defaulting to searching for the closest match, “false” makes sure VLOOKUP is only returning values if there is an exact match.
You’ll have an easier time running VLOOKUP from another Excel sheet if you keep both sheets open side by side. That way, you can simply click in your second sheet to choose your data range.
Step 4: Check the result
The last step is always to check your work! Once you’ve entered the VLOOKUP formula, check the cell to make sure it’s been populated correctly. If you get an error, you might have to go back and correct the formula. But if the data you get isn’t what you expected, there might be an issue with your data range.
How to get more data from another sheet than VLOOKUP
VLOOKUP is a great way to quickly look up data from one sheet and feed it to another. But it’s not necessarily always the best way to keep your records up to date. That’s because you need to manually input the formula any time you need that data. And if you’re not an Excel aficionado, that can get tricky.
But there’s a better solution.
Unito is a no-code two-way sync tool with some of the deepest integrations for the most popular tools on the market, including Excel, Google Sheets, Airtable, Notion, and more. With a Unito flow, you can sync records from spreadsheet to spreadsheet, and they’ll be automatically updated in both tools in real time. It’s a bit like VLOOKUP, except you don’t need to know any formulas — and it works across tools
Curious? Find out what Unito’s Excel integration can do here.
Using VLOOKUP in two Excel sheets FAQ
Here are the answers to some of the most common questions about using VLOOKUP with two Excel sheets.
How to do a VLOOKUP in two different Excel web files?
Unfortunately, you can currently not use the VLOOKUP function in one Excel web file to import data from another Excel web file. This is only possible on the desktop version of Excel.
What’s the difference between VLOOKUP and XLOOKUP?
While VLOOKUP looks for data in a single column, XLOOKUP will give you more flexibility. You can choose to search either in a row or column as well as handle missing data with an optional argument. You’ll also be able to use wildcards for better partial value matching, and XLOOKUP formulas are less likely to break than their VLOOKUP equivalents.
What’s the difference between VLOOKUP and HLOOKUP?
VLOOKUP looks up data in spreadsheet columns while HLOOKUP will look up data in rows — and send it to another row.
What’s the difference between VLOOKUP and INDEX MATCH?
Both formulas will look up data in your spreadsheets, but VLOOKUP will look for a value in the leftmost column of your sheet while INDEX MATCH will look for a specific value in multiple columns.
What are some other formulas that work in Excel?
In this blog post, we break down some of the most popular formulas for Google Sheets and how they work. Most of them are 100% compatible with Excel, too.