How to VLOOKUP From Another Sheet?
(Note: This guide on how to do VLOOKUP from another sheet is suitable for all Excel versions including Office365)
VLOOKUP is a widely used Excel lookup formula used to retrieve information from a large worksheet. You might have used it to search for information inside a sheet. But, did you know that you can use it to look up information across sheets and workbooks?
In this guide, you will learn how to use the VLOOKUP function to retrieve data from another sheet.
- VLOOKUP Across Worksheets
- How to VLOOKUP from Another Sheet?
- VLOOKUP From Another Workbook
- VLOOKUP Across Worksheets Using IFERROR
VLOOKUP Across Worksheets
Usually, people use multiple worksheets to declutter their work. Sometimes, they may do this to protect sensitive information or restrict access to others. In such cases, a simple VLOOKUP may not be possible.
That is why, in such a situation, VLOOKUP is used across worksheets.
For this to work properly, there has to be at least one common column like the emp_id in the worksheets for the VLOOKUP to refer and pull the result.
Syntax
The function takes a lookup value, table reference, and column index as mandatory parameters. The last parameter is optional, which specifies the return value. It could either return an exact match or an appropriate match.
In simpler words, VLOOKUP searches for a value that is mentioned in the formula and returns a matching corresponding value from another column.
It is mandatory that the VLOOKUP function has a known value as an argument. Now, let’s look at the syntax and understand it with an example.
VLOOKUP ( lookup_value,’sheet name’! table_array,col_index_num,[range_lookup])
Parameters and their Explanation
| Parameters | Explanation |
| lookup_value | This is the value to search across the cells to get the match and return the corresponding value either from the adjacent column from the same worksheet or from another worksheet. |
| sheet name! table_array | Mention the name of the worksheet and also the starting value and the ending value of the cells where the function has to look up. |
| col_index_num | The value in this argument represents the index of the column to look into. |
| range _lookup | It accepts 2 values, one is TRUE and another is FALSE.If TRUE is mentioned in the formula, then the function returns the closest match if it is unable to get the exact match.If FALSE is mentioned, the function returns only the exact match. |
Related:
VLOOKUP for Dummies (or newbies)
Compare Two Lists Using VLOOKUP
VLOOKUP vs INDEX/MATCH vs XLOOKUP
How to VLOOKUP from Another Sheet?
Let’s understand how VLOOKUP works across worksheets with this example. We have a table named ‘Emp Details’ table and got a set of data in each column.
There’s another table called the ‘Personal Details’ Table.
You want to know the date of birth of one of the employees named ‘Zara’ which is on the ‘Personal’ sheet. Let’s see how VLOOKUP gets that detail in the ‘Emp Details’ sheet.
Make use of the VLOOKUP formula, =VLOOKUP(A5, ‘Personal’! A2:D8,4, FALSE).
Please note that we refer to the other sheet using its name: ‘Personal’!, inside the VLOOKUP formula.
Follow it up with the range of cells that need to be referred to get the required result.
Here, in this example, A2:D8 cells contain the result that we are looking for. A2 contains the lookup_value which is a common column in both the worksheets. Mention the col_index_num as 4 as the DOB that we are looking for is in the fourth column from the lookup_ value.
As mentioned earlier, have the parameter for range_lookup as FALSE for the function to return the exact value.
Also Read:
XLOOKUP Google Sheets – 4 Best Alternatives!
The Excel CHOOSE Function – 4 Best Uses
Combine numbers and words in Excel 2013 using the VLOOKUP Function
VLOOKUP From Another Workbook
VLOOKUP from another workbook works the same way, but it needs one more additional piece of information: The Other Workbook’s name in square brackets. The syntax is:
=VLOOKUP(lookup_value, [book1.xlsx]worksheet_name!cell_range, col_index, range_lookup])
Here, just replace the name book1.xlsx with the name of the workbook that contains the relevant information you are looking for. And follow it up with the appropriate sheet name.
VLOOKUP Across Worksheets Using IFERROR
You can use VLOOKUP to lookup more than two worksheets using the IFERROR function. Multiple worksheets can be nested inside several IFERROR functions and the worksheets can be checked one by one.
If the VLOOKUP function finds the result, it displays it. Or else it searches in the next VLOOKUP function. If none of the VLOOKUP functions is able to find a value, then it returns a ‘Not Found’ message.
Let’s look at this example. There are worksheets ‘sales1’ and ‘sales2’ which contain a different set of details regarding the sales.
We want the report of all the sales in the worksheet ‘report’. We make use of the VLOOKUP formula to look up the worksheets ‘sales1’ and ‘sales2’ and return them in the ‘Report’ worksheet.
The formula to do it is =IFERROR(VLOOKUP(A2,sales1!$A$2:$D$3,4, FALSE), IFERROR(VLOOKUP(A2,sales2!$A$2:$D$3,4, FALSE),” Not found”))
In the example above, the VLOOKUP formula is applied in cell C2 of the worksheet ‘Report’. The lookup_value is A2 which contains the value 1. This value is first searched in the sheet ‘sales1’ from the cell range that is mentioned in the formula.
In the ‘sales1’ worksheet, the 4th column is looked up as we have specified the value 4 in the col_ind_num.
We get the result as 30 and the function returns that value in the ‘Report’ worksheet.
Similarly, the VLOOKUP parses the lookup_value 2 and returns the respective value from the worksheet ‘sales2’.
In the same example, you can see that the VLOOKUP function takes the lookup_value ‘3’ and looks for it first in the ‘sales1’ worksheet. It doesn’t find that value.
The first part of IFERROR turns out to be false so it proceeds to execute the remaining part of the formula. It looks for the value ‘3’ in the ‘sales2’ worksheet, finds the value, and returns it back.
I have included an extra value ‘5’ just to illustrate what would happen if the VLOOKUP function is not able to search that value in any of the worksheets specified.
The lookup_value ‘5’ cannot be found in either the ‘sales1’ or ‘sales2’ worksheet and therefore returns the message ‘Not found’.
Suggested Reads:
Getting Started with Power Pivot: Advanced Excel
Advanced PivotTables – 1 Hour Crash Course
Closing Thoughts
In this guide, I have explained how to use the VLOOKUP function across different worksheets, across different workbooks, and using IFERROR to reference across multiple worksheets.
It will be very rewarding to learn these functions and use them across your worksheets. There are more interesting features to be explored in the VLOOKUP formula. I will explain them in future blogs.
For more high-quality guides on Excel, kindly visit our resources section.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
