Have you ever tried using the VLOOKUP function?
If not, then here in this post you will get to know how you can use Excel VLOOKUP from another sheet with ease. Basically, VLOOKUP is a well-known function used to reference columns from the same sheet or you can even use it to refer to another sheet or workbook. You will find the reference sheet similar to the reference cell, however; the table array and index number are taken from another sheet or workbook.
Syntax:
=VLOOKUP(lookup_value, sheet_range, col_index_num, [range_lookup])
Let’s have some explanation of the arguments used in this syntax:
Lookup_value: here the value you need to find in another sheet is added.
Sheet_range: here the range of cells used in another sheet with the data needed to retrieve is added.
Col_index_num: here the column number in the sheet_range related to the data needed to get back is added.
[range_lookup]: here the optional value TRUE or FALSE is added.
When the TRUE value is not found, VLOOKUP can either give you an exact or estimated match.
In case of FALSE, VLOOKUP will give you an exact match.
VLOOKUP from Another Sheet but Same Workbook
Let’s copy the result table to another sheet from the same workbook.
- Choose the VLOOKUP value as cell A2 in the Result Sheet that opens up the VLOOKUP formula.
- You will see the table array on the other sheet. Choose the Data Sheet.
- The formula in the table array does not have a table reference, however, it does have the sheet name.
- Remember that you don’t have to enter the sheet name manually. Once the cell is selected in another sheet, you will see the sheet name by default with the cell reference of that sheet.
- When the range is selected in other sheets, you can lock the range by typing the F4 key.
- You are not supposed to go back to the real sheet where you have applied the formula. End the formula here and put the column reference number along with the range lookup type.
- You will get the results from the other sheet now.
VLOOKUP from Different Workbooks
Now, you know how to get data from different sheets of the same workbook. Moving forward, let’s learn how you can get data from a different workbook. Here we have two workbooks; one is Data Workbook and the other one is Result Workbook.
From these two workbooks, we’ll get the data from the Data workbook to the Result Workbook.
Follow the steps given below:
- In the Result Workbook, open the VLOOKUP function and choose the lookup value.
- Open the real data workbook and choose the table array.
- Press CTRL + Tab. this command can switch you between all the opened Excel workbooks. You will see the table range with Workbook Name, Worksheet Name, and data range given in the workbook. Now, it is time to lock the table array and Excel will do it automatically.
- For the final results, you need to enter the column index number with range lookup. Close the real workbook to check the formula.
You will notice the complete file and subfile names. Now you will see the results.
Things to Consider about Excel VLOOKUP from Another Sheet
- You have to clear VLOOKUP formulas when getting the data from a different workbook. When the workbook is deleted by mistake the data will be lost.
- The table array range must be locked down when getting data from the same sheet or from different sheets but from the same workbook.
- The table array range must also be locked down when the formula is used for another workbook. The formula by default makes it an absolute reference.
Final Thoughts
You are familiar with all the best approaches you can apply in Excel VLOOKUP from another sheet in the same workbook or a different workbook. Try the above-mentioned methods to see what results you will get by using your dataset. Keep trying and continue practicing.
