How to VLOOKUP From Another Sheet in Excel
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 to vlookup from another sheet.
How to do vlookup from another sheet in Excel
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 Excel vlookup from another sheet for the cars that these users own.
Here is what you need to do:
- Type
=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).
=vlookup(A2,dataset!A2:F101,4
- Add a comma/semicolon and enter FALSE to return the exact match. Close the bracket and hit enter
=vlookup(A2,dataset!A2:F101,4,false)
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:
=VLOOKUP(A2,dataset!$A$2:$F$101,4,false)
This is your formula to vlookup Excel from another sheet. Instead of dragging it, you can use an array formula.
- You need to specify an array as a lookup value
=VLOOKUP(A2:A66,dataset!$A$2:$F$101,4,false)
- Then select an array where to return the matching values, insert the VLOOKUP from another sheet formula into the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac)
Did you like this guide on Excel vlookup from another sheet?
In this tutorial, we’ve only covered one case – how you can vlookup Excel from another sheet. Meanwhile, there are many other ways you can benefit from the Excel VLOOKUP function. Check out some of them.
