If your lookup range and lookup value are kept in different Excel workbooks, then don’t worry about it because VLOOKUP can get things done with ease. Now, if you wish to VLOOKUP workbooks via Excel Online, well, you won’t find much luck here.
At the end of the day, while Excel Online is a worthy product, it lacks many of the advanced features found in the desktop version of the popular spreadsheet application. If you do not have Excel installed on your computer, then we suggest subscribing to Office 365 right away.
How to do VLOOKUP in Excel with two sheets
To use VLOOKUP to find data in another Excel workbook requires multiple workbooks’ relevant data. Once you have them, follow these steps.
- Open the Excel app
- Open the relevant Excel document
- Add data for reference
- Add formula and select data
- Go to the workbook with the data
- Select the data you want to search through
- Add formula
- Find the relevant values from other workbooks
1] Open the Excel app
The first thing you should do before anything is to open the Microsoft Excel application.
You can do this by clicking on the Excel icon located on the desktop.
Alternatively, you can open it by going to the All Apps section.
2] Open the relevant Excel document
Ensure that this document contains two or more workbooks.
One or more with the data you want to find, and the other will be the primary workbook.
3] Add data for reference
In the primary workbook, you must add data for reference if you haven’t already.
As you can see from the picture, our reference data has already been included.
4] Add formula and select data
The next step is to add the formula needed to get things moving in the right direction.
Go ahead and type, =VLOOKUP(
From there, you must now click on the relevant data to have it included in the formula, then add a comma.
So, if you decided to go with A4, then the formula will look like this: =VLOOKUP(A4
5] Go to the workbook with the data
After you’ve typed the formula as above, you must now move over to the workbook that contains the data you want to search through.
Go to the very bottom of your workbook and select the relevant tab.
6] Select the data you want to search through
From within the newly selected workbook, please highlight the data you want to search through.
It can be everything or just a selected few, it doesn’t matter.
7] Add to the formula
The next step right after selecting the data you want to work with, is to type a comma.
So, your formula should extend to look like the following:
=VLOOKUP('Example 1'!A4,'Example 1 (Solution)'!A2:I16,
8] Find the relevant values from other workbooks
Finally, we are going to locate the values from the workbook with the data for display in the primary workbook.
To do this, you must determine which column of data must be shown.
As you can see, we have gone with column number 5, so the final formula should look similar to the following:
=VLOOKUP('Example 1'!A4,'Example 1 (Solution)'!A2:I16,8)
The information from column 5 of the workbook with all the data should now be visible in the primary workbook.
READ: How to remove Numbers in Excel from the left
Can you do a VLOOKUP between two workbooks?
Yes, you can do this. Folks normally accomplish this task for the purpose of keeping things organized in separate workbooks. This is made easy due to VLOOKUP and what it is capable of doing.
Why is VLOOKUP not working in two different workbooks?
You need to ensure the external references that link one workbook to another workbook are correct. Furthermore, you must verify the formula in use contains the full path, or there will be problems with the end results.
