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. The data was imported to the sheet titled “dataset“.
Read more about Microsoft Excel integrations for data export on a schedule.
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)
How to automate data import to Excel
Are you still getting your data to Excel by copying and pasting your records for 10 minutes a day? If yes, it seems you haven’t automated this process so far, which could save you over 3 hours a month! With Coupler.io, you can automate data flow from 50+ sources to Excel without coding. It also enables you to sync data between your sheets, eliminating the need for using formulas or macros.
Why not see for yourself? Choose your source app and click Proceed to start for free.
Additionally, you can set a custom schedule for automated data refresh. This way, you’ll have your worksheets continuously updated according to the latest changes.
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.
