Function Problems

How to Automatically Populate a Customer Name from an Account Number in Excel

Phi Hung Vo Oct 12, 2026 869 views

Question details

The user wants to retrieve and display a customer's name automatically in one worksheet when entering their corresponding account number, pulling the data from a master customer list.

How to Automatically Populate a Customer Name from an Account Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Data entry and cross-referencing records between different worksheets to save time and reduce manual errors.
Observed behavior: The goal is to have the customer name cell auto-fill immediately based on the entered account number.

Before you start

Ensure you have a master worksheet named 'Customers' (or similar) with account numbers in one column and customer names in another, and confirm there are no duplicate account numbers in your master list.

Solution 1Recommended

Use the XLOOKUP Function (Recommended for Newer Versions)

XLOOKUP is the most modern, flexible, and robust way to find data in a table or range by row, perfectly suited for auto-populating fields.

XLOOKUP replaces older lookup functions by defaulting to exact matches and allowing you to natively specify what should be displayed if the account number is not found, without needing additional error-handling formulas.

Select Destination Cell

Click on the cell in your active sheet where you want the customer name to automatically appear (for example, B2).

Enter the XLOOKUP Formula

Type =XLOOKUP(A2,Customers!$A:$A,Customers!$C:$C,"") into the formula bar. In this formula, 'A2' is where you type the account number, and it searches column A of the 'Customers' sheet to return the corresponding name from column C.

Apply to Remaining Rows

Press Enter to see the result. Click the cell again and double-click or drag the small fill handle at the bottom-right corner to copy the formula down your column.

Use the XLOOKUP Function (Recommended for Newer Versions)

Blank Return on Empty: The "" at the end of the formula ensures that if the account number is blank or not found, the cell will remain neatly blank instead of showing an ugly error.

Solution 2

Use the VLOOKUP Function with IFERROR

VLOOKUP is widely supported across all spreadsheet software versions and is highly reliable for vertical data searching.

Efficient Data Management

Auto-Populate Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced lookup functions like XLOOKUP and VLOOKUP, enabling you to build automated data-entry workflows effortlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your customer database.
2. Input the Lookup Formula: Select the target cell and enter your preferred XLOOKUP or VLOOKUP formula exactly as you would in Excel.
3. Drag to Apply: Use the fill handle to drag the formula down your dataset to automatically populate names for all account numbers.

100% compatible with Microsoft Excel formats (.xlsx, .xls) and formulas.Native support for advanced functions including XLOOKUP to auto-populate data easily.Lightweight architecture ensures smooth handling of large customer databases.Completely free to use with a familiar and intuitive user interface.

Frequently Asked Questions

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

The #N/A error means the formula cannot find a matching account number. Ensure the account number exists in your master list, check for hidden spaces (you can use the TRIM function to remove them), and confirm that both cells are formatted identically (e.g., both as Text or both as Numbers).

Can I pull customer data from a completely different workbook file?

Yes, you can reference external workbooks. Keep both the destination file and the master database file open. When writing your formula, click into the other workbook's column. Excel or WPS Spreadsheet will automatically generate the correct external file path in your formula.

Does the master list need to be sorted for these lookup functions to work?

If you are looking for an exact match (using FALSE in VLOOKUP or the default XLOOKUP settings), the data does not need to be sorted. However, if you are looking for an approximate match, the lookup column must be sorted in ascending order.

Excel & Data Problems