If you want to maintain a large collection of data without having to mess it up you can use vlookup in Excel sheets. If you are new to this term and don’t know what and how to do then this article is perfect for you. In this article, you are going to learn how to do vlookup in Excel with two spreadsheets.
Table of Contents
What is Vlookup?
Before knowing how to do vlookup in Excel with two spreadsheets you have to know what is vlookup. To locate specific information in cells MS Excel spreadsheets have a function named vlookup. This helps you to manipulate the data in whatever way you want like pulling and organizing data, locating it, etc you can use this tool to locate large data files on different or same worksheets without facing major issues. You can maintain your data as well as manage and locate pieces of information. You can use vlookup for one spreadsheet, two spreadsheets, or even multiple spreadsheets. In this article, we will be discussing how to do vlookup in Excel with two spreadsheets.
How To Do Vlookup In Excel With Two Spreadsheets?
Finally, you are going to know how to do vlookup in Excel with two spreadsheets. You need to know about a lot of components of vlookup and use them accordingly. you need to know a lot of steps as well.
1. Know Your Components
For two spreadsheets you would want to know the sheet range for the data you are going to put in rather than doing it in a table array as you do it for one sheet.
- lookup_value – it is the value that you search for in the spreadsheet.
- Sheet!range – this is used to provide details about the array and table that you want to collect and manage the information. For example, “sheet2!A1:D4”.
- col_index_num – col should suggest columns and indeed is used to provide details about the column where you want to gather data. This column number matches with the column number or sequence in the table array that was selected for data input and management.
- [range_lookup] – it is an optional component where you can include “TRUE” to collect as many approximate matches as possible or “FALSE” to create an exact match.
The formula to create vlookup in Excel with two spreadsheets might look like this-
VLOOKUP(lookup_value, Sheet_name!range, col_index_num, [range_lookup])
2. Specify Your Requirement Between Sheets
When you want to enter data in a specific cell you can also enter the exact criteria required for searching it. You can understand it easily using an example, to gather data or collect data on a summary tab from a worksheet named “Results”, with the help of table data ranging from cells A3 through E8, you have to type this formula-
=VLOOKUP(A3, Results!A3:E8, 3, FALSE)
That shows you will collect data from cell A3 from the worksheet “Results” within the third column in the table array A3 through E8.
How To Perform VLOOKUP Between Workbooks?
Now that you already know how to do vlookup in Excel with two spreadsheets you might also want to know how to do the same between two workbooks. It’s almost similar to the previous method just you need to specify the name of the workbook you want to use along with the spreadsheet you are using to collect data. The formula to do that might look like this –
VLOOKUP(lookup_value, [workbook_name]Sheet_name!range, col_index_num, [range_lookup])
Tips On To Do Vlookup In Excel With Two Spreadsheets
You already read about the components and way of writing the formula for vlookup in Excel sheets. For a better experience here are some tips for you-
- You need to use quotations for special characters while writing the formula for vlookup. When your sheet name has special characters or spaces as well as single or double quotes around its name will make sure that you get appropriate search results. For example, “=VLOOKUP(A2, ‘Transportation’!A2:E6, FALSE)”.
- You can actually highlight the table range while navigating through the spreadsheets from where you want the data instead of just manually entering the table range along with the table name while writing the formula for vlookup. By doing this you are ensuring that you get the most precise data search results as far as possible.
- This tool, vlookup is effectively useful when you are searching for relevant data. For example, if you want to gather student information from different tabs make sure that any column is equivalent like student ID or anything of that sort, so that you can prevent any errors from occurring during the search process.
- To find exact matches you need to include “FALSE” or “0” as the range lookup value. Vlookup’s default setting is for approximate matches. If you save your data in the source table in a different way than you have done in the destination table then your data will be entered based on row number rather than the data indicated in your table.
Example Of A VLOOKUP Between Two Sheets
Mount Litera Zee School records its monthly attendance in a spreadsheet. Each month has a separate tab. Here are the spreadsheets with the required details:
Sheet name: February
|1||Student name||Attendance %|
Sheet name: Summary
|1||Student name||Attendance % (Jan)||Attendance % (Feb)|
=VLOOKUP(A2, February!A2:B5, 2, FALSE)
This enters 20% on the summary tab.
You can select the cell and drag it across the next three rows to enter the data for those fields. After completion, the summary tab would look like this –
|1||Student name||Attendance %|
You have to repeat the steps for each month to complete the table.
Now you can easily find or locate data in two spreadsheets without facing major problems and can work peacefully and with higher speed than usual. You can manage and gather data wherever you want. Following the given tips would speed up the process even more.
Frequently Asked Questions
1. Can VLOOKUP be done between two file workbooks in Excel?
For look-up and reference, VLOOKUP is the best tool. Normally you cannot look for values across multiple workbooks but by entering an indirect function into the VLOOKUP formula you can check it.
2. What is lookup_value?
lookup_value – it is the value that you search for in the spreadsheet.
3. What is range_lookup?
[range_lookup] – it is an optional component where you can include “TRUE” to collect as many approximate matches as possible or “FALSE” to create an exact match.
4. Is it possible to do VLOOKUP in two spreadsheets?
Yes! It is possible to do so. The above article is all about it. You will get the entire procedure there.