If your lookup value and lookup range are stored in different sheets of one Excel Workbook, you can use VLOOKUP without any problems. Here is what you should do.
How to do vlookup in Excel with another sheet
We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automatic data exports from multiple apps and sources.
Read more about Microsoft Excel integrations for data export on a schedule.
The data was imported to the sheet titled “dataset“. In another sheet, titled “users“, we have a list of users. The aim is to look up the cars that these users own.
Here is what you need to do:
=vlookup(in the B2 cell
- Specify the lookup value. You can enter a string wrapped in quotes or reference a cell just like we did:
- Enter a comma/semicolon (depending on the list separator defined under your regional settings), click on the sheet with the dataset you want to look up and select the desired range.
- In the formula bar, add a comma/semicolon and enter the column number, which contains the matching value to return. In our case, the number is 4 (column D).
- Add a comma/semicolon and enter FALSE to return the exact match. Close the bracket and hit enter
To return the matching values for other users, we can drag the formula down – but first we’ll need to lock the table_range using the $ symbol as follows:
Another option is to use an array formula:
- You need to specify an array as a lookup value
- Then select an array where to return the matching values, insert the VLOOKUP formula into the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac)