Function Problems

How to Use Excel Formulas to Find a Name in Another Workbook

Adam Davis Oct 8, 2026 872 views

Question details

The user needs to locate a specific name in a different worksheet or workbook (such as a previous year's member list) and retrieve corresponding details using lookup formulas.

How to Find a Name in Another Excel Workbook and Return Details

Product: Excel
Device & OS: not provided
Scenario: Pulling historical data or related details from an external worksheet into the current working spreadsheet to avoid manual searching.
Observed behavior: Requires the correct syntax and application of lookup formulas to automatically retrieve data across different workbook tabs or files.

Before you start

Ensure both your current workbook and the source workbook (containing the data you want to retrieve) are open. Additionally, check that the names are spelled identically in both lists without hidden spaces, as lookup functions require exact matches by default.

Solution 1Recommended

Use the VLOOKUP Function to Retrieve Details

VLOOKUP is the standard method for searching a value in the leftmost column of a table and returning a value in the same row from a specified column.

The VLOOKUP formula structure is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). You will need to lock the table array reference using absolute references ($) so the range doesn't shift when copying the formula down.

Select the destination cell

Click on the cell where you want the retrieved detail to appear in your current sheet, and type `=VLOOKUP(`.

Select the lookup value

Click the cell containing the name you want to search for (for example, A2), then type a comma.

Define the source data range

Navigate to the previous year's worksheet or external workbook. Select the columns containing the names and the details you want to retrieve (e.g., columns A and B). The formula will update to include the sheet name, such as `'PreviousYearSheet'!$A:$B`. Type another comma.

Enter the column index number

Type the number of the column in your selected range that contains the data you want to return. If names are in column A (1) and details are in column B (2), type `2`, followed by a comma.

Specify an exact match

Type `FALSE` to ensure the formula only returns details for an exact name match. Close the parenthesis so the formula looks like `=VLOOKUP(A2,'PreviousYearSheet'!$A:$B,2,FALSE)` and press Enter. You can now drag the fill handle to copy the formula to other cells.

Use the VLOOKUP Function to Retrieve Details

Absolute References: If you highlight specific cell ranges (like A1:C100) instead of entire columns (like A:C), be sure to press F4 to make the range absolute ($A$1:$C$100) before hitting Enter.

Solution 2

Use the XLOOKUP Function for Flexible Searching

Available in newer versions of Excel, XLOOKUP is a more robust alternative to VLOOKUP that allows you to search in any column without counting column numbers.

Effortlessly Manage Cross-Workbook Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions, including VLOOKUP and XLOOKUP, allowing you to easily pull details from multiple workbooks. It provides a highly intuitive formula environment to prevent errors when referencing external data.

1. Open your workbooks in WPS Spreadsheet: Launch WPS Office and open both the target workbook and the source workbook. They will open conveniently in separate tabs within the same window.
2. Insert the lookup formula: Select your target cell, navigate to the Formulas tab, and click Insert Function to search for VLOOKUP, or simply type `=VLOOKUP(` directly into the cell.
3. Navigate across tabs easily: When prompted to select the table array, click the source workbook's tab at the top of the WPS window, highlight the data range, and finish configuring your column index and exact match settings.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx, .xls, .csv).Lightweight performance ensures fast calculations even when linking massive datasets across multiple open workbooks.Built-in function autocomplete and intelligent syntax highlighting make writing complex VLOOKUP formulas straightforward.Free to use with a familiar, easy-to-navigate tabbed interface for managing multiple files in a single window.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

The #N/A error occurs when the exact name cannot be found in the source sheet. Check for accidental hidden spaces before or after the names, ensure you are using 'FALSE' at the end of the formula for an exact match, and verify that the column containing the names is the very first column in your selected table array.

Can I use VLOOKUP if the source workbook is closed?

Yes, VLOOKUP can pull data from a closed workbook, but the formula will automatically update to display the full file path of the source file. If the source file is moved, renamed, or deleted, the link will break. It is highly recommended to keep both workbooks open while initially setting up the formulas.

How do I return multiple columns of data at once for the same name?

If you are using VLOOKUP, you will typically need to create a separate formula for each column you want to return by changing the column index number (e.g., changing 2 to 3 for the next detail). Alternatively, if you use modern array functions or XLOOKUP, you can select multiple columns in the return array to spill the results across several cells automatically.

Excel & Data Problems