Function Problems

How to Use XLOOKUP to Return an Account Name from an Account ID

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to retrieve an Account Name from a matching Account ID inside a spreadsheet table using a structured-reference lookup formula.

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up text data (Account Name) dynamically based on a unique identifier (Account ID) located in another structured table.
Observed behavior: The user needs the correct XLOOKUP function syntax utilizing table and column names to pull the matching information without errors.

Before you start

Ensure your data range is formatted as a Table (Insert > Table) and verify that the column headers exactly match the text used in your formula.

Solution 1Recommended

Use XLOOKUP with Structured Table References

Apply the XLOOKUP function using table and column names to accurately retrieve your data.

Using structured references in your formula makes it easier to read and automatically adapts when you add or remove rows in your source table. The XLOOKUP function will search the lookup array for your ID and return the corresponding value from the return array.

Identify your lookup elements

Confirm the exact name of your lookup table (e.g., 'Table1'), the lookup array column header ('Account ID'), and the return array column header ('Account Name').

Enter the XLOOKUP formula

Click on the cell where you want the Account Name to appear and type `=XLOOKUP([@[Account ID]],Table1[Account ID],Table1[Account Name],"")`.

Execute the formula

Press Enter. The formula will search for the specific Account ID in Table1's Account ID column and return the matching Account Name. If no match is found, it will display a blank cell.

Check Your Syntax: Ensure that the table name 'Table1' and the column names '[Account ID]' and '[Account Name]' match your actual spreadsheet table headers exactly to avoid a #REF! or #NAME? error.

Efficient Data Lookups

Easily Manage Advanced Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP and structured table references, making data retrieval fast, seamless, and highly accurate.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the tables.
2. Format data ranges as tables: Select your data range, navigate to the Home tab, and click 'Format as Table' to enable structured references.
3. Apply your XLOOKUP formula: Type your XLOOKUP formula referencing the table columns exactly as you would in Microsoft Excel to seamlessly retrieve your account names.

Native support for XLOOKUP, VLOOKUP, and dynamic table referencesFull compatibility with Microsoft Excel workbook formats (.xlsx)Lightweight application with fast formula calculation for large datasetsFree to download and use for everyday data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #NAME? error?

This usually happens if there is a typo in the formula name or the table/column names referenced in the formula. Check your spelling and ensure your data is actually formatted as a Table.

What does the empty string "" do at the end of the XLOOKUP formula?

The "" serves as the 'if_not_found' argument in the XLOOKUP function. It tells the formula to return an empty blank cell instead of an #N/A error if the Account ID is not found in the table.

Can I use XLOOKUP if my data is not formatted as a Table?

Yes, you can use standard cell references (like A2:A100) instead of structured references (like Table1[Account ID]). However, structured references are generally easier to read and automatically expand when new data is added.

Excel & Data Problems