Function Problems

How to Retrieve an Adjacent Excel Cell Value by Name

Amos Gikunda Oct 1, 2026 869 views

Question details

The user needs to enter a person's name in one cell and automatically return a corresponding number or value from an adjacent column located in another table.

How to Retrieve an Adjacent Excel Cell Value by Name

Product: Excel
Device & OS: not provided
Scenario: Looking up related data (like an ID number, phone number, or department) based on a specific name input from a separate data source.
Observed behavior: Automatically fetching and displaying the related adjacent value when a target name is provided.

Before you start

Ensure both tables have consistent spelling and formatting for the names (without extra spaces) to prevent lookup mismatch errors.

Solution 1Recommended

Use the XLOOKUP Function (Recommended)

XLOOKUP is the most robust and flexible function for looking up adjacent data in newer versions of Excel.

Available in Microsoft 365, Office 2021, and newer spreadsheet programs, XLOOKUP allows you to search in any direction and easily define what happens if a value is not found.

Select the destination cell

Click on the empty cell where you want the retrieved adjacent value to appear.

Enter the XLOOKUP formula

Type the formula =XLOOKUP(A2, SourceNames, SourceValues, "") into the formula bar.

Define your specific ranges

Replace 'A2' with the cell containing the name you are searching for. Replace 'SourceNames' with the column range containing the list of names, and 'SourceValues' with the adjacent column range containing the target values.

Execute the lookup

Press Enter to run the formula. If the name is found, the corresponding value will instantly appear.

Lookup Direction Flexibility: Unlike older functions, XLOOKUP can retrieve data to the left or right of your search column without restructuring your data table.

Solution 2

Use the VLOOKUP Function

If you are using an older version of Excel that does not support XLOOKUP, VLOOKUP is the standard alternative.

Seamless Data Lookup in WPS Office

Retrieve Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data functions like XLOOKUP and VLOOKUP, making data retrieval smooth and straightforward. You can manage complex datasets and execute lookups precisely as you would in other major spreadsheet software.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing data workbook.
2. Select Target Cell: Click on the cell where you want the adjacent lookup value to appear.
3. Apply Lookup Formula: Input your =XLOOKUP() or =VLOOKUP() formula exactly as you normally would, and press Enter to instantly retrieve the data.

Fully compatible with Microsoft Excel (.xlsx) file formats and lookup formulas.Native support for both modern XLOOKUP and traditional VLOOKUP functions.Free, lightweight, and optimized for fast performance on large datasets.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error?

This error occurs if the name you are searching for does not exist in the source table, or if there are trailing spaces in either text string. Ensure exact matches and always use the FALSE parameter at the end of your formula for exact matching.

Can I look up a value to the left of the name using VLOOKUP?

No, VLOOKUP can only search the leftmost column of a specified range and return values to the right. To look up data to the left, you must use XLOOKUP or an INDEX/MATCH combination.

How do I handle missing names so the cell doesn't show an error?

In XLOOKUP, you can include a custom message or blank output in the 'if_not_found' argument, such as inserting "" to leave the cell blank. If using VLOOKUP, wrap your formula in an IFERROR function, like this: =IFERROR(VLOOKUP(...), "").

Does WPS Office support the XLOOKUP function?

Yes, current versions of WPS Spreadsheet fully support the XLOOKUP function, providing seamless formula compatibility with files created in newer versions of Microsoft Excel.

Excel & Data Problems