How to Use VLOOKUP or XLOOKUP Across Workbooks in Excel
Question details
The user needs to retrieve a company name based on an order number by looking it up in a different, unsorted workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific value across separate Excel files when the source data is unsorted.
- Observed behavior
- The user wants to populate a cell with data matched from an external file accurately, regardless of the source data's sort order.
Ensure that both your current workbook and the source workbook are open when creating the formula so you can easily select the correct ranges with your mouse.
Use VLOOKUP for an Exact Match Across Workbooks
The classic VLOOKUP function is ideal for finding an exact match when your lookup value is in the first column of the source data.
VLOOKUP searches for a value in the first column of a table array and returns a value in the same row from another column. By setting the range_lookup argument to FALSE, you force an exact match, which means the source data does not need to be sorted.
Click on the cell (e.g., E2) where you want the retrieved company name to appear.
Type `=VLOOKUP(G2, ` where G2 is the cell containing the order number you want to look up.
Switch to the open source workbook and highlight the data range containing both the order numbers (in the first column) and the company names. The formula will update to look something like `[Workbook2.xlsx]Sheet1!$A:$D`.
Type `, 2, FALSE)` assuming the company name is in the second column of your selected range. The FALSE argument ensures an exact match.
Press Enter to complete the formula and retrieve the company name.
Use XLOOKUP for Flexible Searches
XLOOKUP is a modern alternative that doesn't require the lookup value to be in the first column and handles missing data gracefully.
Perform Cross-Workbook Lookups Effortlessly with WPS Spreadsheet
WPS Office fully supports advanced formulas like VLOOKUP and XLOOKUP. You can seamlessly link data across multiple workbooks to automate your workflow without paying for expensive software.
- 1. Open your workbooks: Open both your main spreadsheet and the source data spreadsheet in WPS Office.
- 2. Start the formula: In your main sheet, select the target cell and type `=VLOOKUP(` or `=XLOOKUP(`.
- 3. Select ranges across tabs: Click the document tab of the source spreadsheet to select your lookup ranges directly with your mouse.
- 4. Complete the function: Finish the formula arguments and press Enter to instantly retrieve your data.
Frequently Asked Questions
Why does my VLOOKUP return an #N/A error?
An #N/A error means the exact lookup value was not found in the source data. Check for trailing spaces, mismatched data types (e.g., text vs. numbers), or ensure you selected the correct lookup column.
Do I need to keep the source workbook open for VLOOKUP to work?
No, once the formula is created, VLOOKUP will display the last fetched value even if the source workbook is closed. However, to update the data, you may need to enable external links or open the source file.
Why is XLOOKUP better than VLOOKUP?
XLOOKUP can search in any direction (left or right), defaults to an exact match, allows you to specify a 'not found' message easily, and generally performs faster on large datasets compared to VLOOKUP.
Does the source data need to be sorted for these formulas?
No. As long as you use an exact match (by adding FALSE in VLOOKUP or using the default XLOOKUP settings), the order of the source data does not matter.
