Function Problems

How to Insert a Username from Another Excel Table by Matching an ID

Natalie Taylor Oct 22, 2026 868 views

Question details

The user needs to populate a spending table with usernames by referencing another table and matching their unique IDs.

How to Insert a Username from Another Excel Table by Matching an ID

Product: Excel
Device & OS: not provided
Scenario: Managing a spending spreadsheet where IDs repeat across rows, and needing a formula to automatically pull the correct username for each row from a separate reference table.
Observed behavior: Requires an automated formula like XLOOKUP or VLOOKUP to cross-reference data and fill in the missing usernames based on matching IDs.

Before you start

Before applying lookup formulas, ensure that the reference table containing the IDs and usernames has no duplicate IDs, and that the ID columns in both tables share the same data format (either both text or both numbers).

Solution 1Recommended

Match IDs Using the XLOOKUP Function

XLOOKUP is the most robust and modern approach for looking up values, especially when working with structured tables. It natively handles missing data without requiring extra error-handling formulas.

If you are using a newer version of Excel that supports dynamic arrays, XLOOKUP is highly recommended. It allows you to search in any direction and specify a default value if the ID is not found.

Select the Target Cell

Click on the first empty cell in the username column of your spending table where you want the matched name to appear.

Enter the XLOOKUP Formula

Type the formula =XLOOKUP([@[ID Number]],Table1[ID Number],Table1[Username],"-"). In this formula, replace 'Table1' with the actual name of your reference table. The "-" ensures a dash is displayed if an ID is missing.

Apply the Formula

Press Enter. Because you are using structured tables, the formula will automatically fill down the rest of the column, populating all usernames.

Tip: Using structured tables (Insert > Table) makes your formulas easier to read and automatically expands when you add new data to the bottom.

Solution 2

Lookup Usernames Using the VLOOKUP Function

If you are using an older version of Excel or working with standard ranges instead of structured tables, VLOOKUP is the traditional alternative to match IDs.

Smart Data Processing

Easily Match and Lookup Data Using WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like XLOOKUP, VLOOKUP, and structured table references, making it incredibly easy to cross-reference datasets and auto-fill your tables without errors.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your spending and reference tables.
2. Select the Target Cell: Navigate to the blank column where the usernames need to be inserted.
3. Apply the Lookup Formula: Type your preferred formula (such as =XLOOKUP or =VLOOKUP) using the formula bar to select the appropriate ranges.
4. Auto-Fill the Data: Press Enter and double-click the fill handle to populate the entire column instantly.

Fully compatible with Microsoft Excel (.xlsx) formats and standard lookup formulasNatively supports modern array functions like XLOOKUP and FILTERIntuitive UI for creating and managing structured tablesFree and lightweight alternative for complex data analysis

microsoft office alternative - wps office

Frequently Asked Questions

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

An #N/A error typically occurs if the specific ID does not exist in the source table, or if there is a data type mismatch. For example, the ID might be formatted as text in your spending table but as a number in the reference table. Ensuring both columns use the same format will usually resolve this.

How can I handle missing IDs without showing an error?

If you are using XLOOKUP, it has a built-in argument for missing data. By adding a value like "-" or "Not Found" as the fourth argument, it replaces the error. If you are using VLOOKUP, you can wrap the formula in an IFERROR function, like this: =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE), "-").

Can I pull username data from a completely different workbook?

Yes. You can reference another workbook in your lookup formulas. Ensure the other workbook is open, begin typing your formula, and click into the external workbook to select your range. Excel or WPS Spreadsheet will automatically format the external file path into the formula.

Excel & Data Problems