How to Use VLOOKUP to Reference Another Sheet or Workbook
Question details
The user needs to extract data using the VLOOKUP function where the lookup value and the target data array are located in different worksheets or external workbooks.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Cross-referencing and pulling data dynamically from multiple tabs or separate spreadsheet files using the VLOOKUP function.
- Observed behavior
- The user wants to successfully build a VLOOKUP formula that fetches values from a different sheet or external workbook without syntax errors.
Ensure both workbooks are open if you are referencing an external file to make selecting the range easier, and verify that the lookup value exists in the first column of your target data range.
Reference Another Worksheet in the Same File Using VLOOKUP
Use this method when your target data table is located on a different tab within the same spreadsheet document.
Referencing another sheet requires including the sheet name followed by an exclamation point before the cell range. Fortunately, the software automatically formats this for you when you click the target sheet.
Select the cell where you want the result to appear. Type =VLOOKUP( to begin the function.
Click the cell containing the value you want to search for, then type a comma.
Click the tab of the other worksheet at the bottom of the screen. Highlight your target data range (e.g., A1:D100) and type a comma. The formula bar will automatically add the sheet name reference, like Sheet2!A1:D100.
Type the column index number (the column containing your return value), a comma, and 0 (or FALSE) for an exact match. Add a closing parenthesis and press Enter.
Reference an External Workbook Using VLOOKUP
Use this method to pull data from a completely separate, external spreadsheet file into your current working document.
Easily Use VLOOKUP Across Sheets with WPS Spreadsheet
WPS Spreadsheet provides an intuitive formula builder and seamless cross-workbook data referencing, making complex formulas easy to write and manage.
- 1. Open files in WPS Spreadsheet: Launch WPS Office and open your spreadsheets. They will conveniently open in separate tabs within the same window.
- 2. Initiate the VLOOKUP function: Select your output cell, type =VLOOKUP(, and select your lookup value.
- 3. Click to select external data: Simply click on the target tab containing your source data and highlight the range. WPS automatically fills in the correct cross-sheet syntax.
- 4. Complete the formula: Add your column index and match type, then press Enter to instantly pull the data.
Frequently Asked Questions
Why does my VLOOKUP to another workbook show a #REF! error?
A #REF! error typically occurs if the column index number entered in the formula exceeds the total number of columns in your selected lookup range. It can also happen if the external workbook was permanently moved, renamed, or deleted after the formula was created.
Do I need to keep the external workbook open for VLOOKUP to work?
No, VLOOKUP can pull data from a closed external workbook. However, when the source file is closed, the formula bar will display the full file path instead of just the file name. Note that recalculating thousands of VLOOKUP formulas linked to a closed workbook may temporarily slow down performance.
How do I lock the referenced range when pulling data from another sheet?
You can lock the range by using absolute references, which means adding dollar signs to the cell coordinates (e.g., Sheet2!$A$1:$D$100). You can do this quickly by pressing the F4 key on your keyboard immediately after highlighting the range while building your formula.
Can I use VLOOKUP across sheets if the sheet name contains spaces?
Yes. When a worksheet name contains spaces, the formula requires the sheet name to be enclosed in single quotation marks (e.g., 'Sales Data'!A1:B10). If you build the formula by clicking the target sheet with your mouse, the software will add these quotation marks automatically.
