If you work with databases on Excel, then you have probably experienced sharing data between different sheets or workbooks. The VLOOKUP function in Excel saves you from having to use the traditional copy/paste method to transfer data between multiple worksheets or workbooks.
How to do a VLOOKUP between two sheets in Excel?
Let’s first break down the syntax of the VLOOKUP function in Excel.
VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])
- lookup_value: the first data cell you will look up.
- table_array: the range of cells you want to retrieve. You need to indicate the name of the sheet or tab, followed by an exclamation point, and then the range of cells (e.g. Sheet 1!A1:D4).
- col_index_num: the column you want to retrieve the data from. You need to indicate the position of the column with a numeric value - 1, 2, 3, etc.
- [range_lookup]: the parameter that will ensure you look up an exact match, by entering the value “FALSE”.
Imagine that you are a car salesperson and you have distributed your car-related data in an Excel spreadsheet across various tabs; for example, each tab includes monthly data. You can select the data you wish from different tabs and import it into one Excel sheet.
- 1. Start in the sheet where you want your VLOOKUP results to be located. In this example, I want to add them to my '2021 ALL' worksheet next to the 'VIN' column.
- 2. Click on the cell where you will enter the VLOOKUP formula. Start typing VLOOKUP. See how Excel prompts you with the formula as soon as you start typing.
- 3. Now you can either type in the formula yourself, or avoid syntax errors by simply selecting the sheets and cells. Click on the cell you want as your lookup_value. In my example, this is the first cell in my ‘VIN’ column.
- 4. Place a comma or semicolon (check your computer’s regional settings to check), followed by the table_array value which can be obtained simply by selecting the dataset you want to look up. Note that we have moved to the ‘JANUARY’ sheet.
- 5. For the col_index_num, you need to manually enter the position of the column you want to import data from. In this case, we want the prices, so ‘4’.
Transfer Data From One Excel Worksheet to Another Automatically
Excel worksheets or workbooks can be linked to automatically update data. Here's how to transfer data from one Excel worksheet to another automaticallyREAD MORE
- 6. The range lookup refers to a true-false value. If you want to lookup an exact match, then you need to select ‘FALSE - Exact match’.
- 7. As soon as you press Enter, you can go back to your original sheet and check that the first cell shows the price value corresponding to the first ‘VIN’ cell we used to VLOOKUP.
- 8. Retrieve the rest of the column value by dragging down from the bottom-right corner of the cell.
- 9. Since we want to keep the data as currency values, go to the drop-down arrow in the currency icon and select the US dollar sign ($).
- 10. And there you have it. Your first VLOOKUP between two sheets in Excel.
So far, you have seen how to do a VLOOKUP in Excel between two sheets within one same spreadsheet. Excel also allows you to use VLOOKUP across multiple sheets in case you want to apply the formula to several sheets simultaneously. We perform this action in combination with the IFERROR formula.
=IFERROR(VLOOKUP(lookup_value,Sheet!range, col_index_num, [range_lookup])
Sometimes our data may be spread out among different Google sheets or workbooks. Here's how to VLOOKUP from another sheet or workbookREAD MORE
How to VLOOKUP between two workbooks in Excel?
Continuing with the use case of this article, let’s say that I now want to add the car owners’ email address to the ‘CAR REGISTRATION’ file, which is kept in another Excel workbook, i.e. ‘CLIENT INFO’. This is how to VLOOKUP data between two workbooks in Excel.
- 1. Open the two Excel workbooks and click on the cell where you want your VLOOKUP results located. Here is my example dataset below containing the car owners’ email addresses.
- 2. Enter the VLOOKUP formula in a cell in your original file. I will locate this in my ‘CAR REGISTRATION’ file, where I will obtain the email address data.
- 3. Click on the first cell you want as your lookup_value. Here, I will click on the first ‘VIN’ cell (A2).
- 4. For the table_array values, go to the other workbook and select the dataset you want to use to VLOOKUP. See how this parameter is now expressed between squared brackets.
Make sure to also include the workbook name between quotation marks if it contains spaces or special characters (‘[CLIENT INFO.xlsx]2021 ALL’!$A$1). Use the dollar sign ($) to lock the range to prevent any changes when transferring the formula between cells, worksheets, or workbooks.
- 5. Enter the position of the column you want to import data from as the col_index_num. Here, I will add the number of the ‘EMAIL’ column (‘4’).
6. Select “FALSE - Exact match’ for the last parameter to return the exact match.
- 7. Close parenthesis and press Enter.
- 8. Drag down to apply the formula to the remaining cells and obtain all your clients’ email addresses.
How to transfer data between multiple Google Sheets?
Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.
Using Layer, you can:
- Share & Collaborate: Automate your data collection and validation through user controls.
- Automate & Schedule: Schedule recurring data collection and distribution tasks.
- Integrate & Sync: Connect to your tech stack and sync all your data in one place.
- Visualize & Report: Generate and share reports with real-time data and actionable decisions.
Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!
Now that you know how to do VLOOKUP in Excel with two different spreadsheets, you understand why it’s one of the most sought-after formulas by those working with data on a daily basis. You can transfer data from one Excel worksheet to another automatically while preventing any changes in the data. To learn more about automatic data transfer between Excel files, why not take a look at our article on how to transfer data from one Excel worksheet to another automatically.
Please note that if you prefer to work online, VLOOKUP does not work between Excel spreadsheets online. This is why you should use Layer - it allows you to upload your spreadsheet to our platform and adapt resourceful functions, such as VLOOKUP, between your original Excel workbooks.
If you want to learn more about Layer, you can start by reading a short guide.