Function Problems

How to Automatically Fill Invoice Customer Details with an Account Number in Excel

Kushani NimanthikaKushani Nimanthika Oct 28, 2026 872 views

Question details

The user wants to create an Excel invoice that automatically retrieves and displays a customer's name, address, and other details when a specific account number is entered.

How to Automatically Fill Invoice Customer Details with an Account Number in Excel
Product
Excel
Device & OS
not provided
Scenario
Creating an automated invoice spreadsheet where entering an account number auto-populates the corresponding customer information from another worksheet.
Observed behavior
Customer details such as name and address should dynamically appear in the invoice template when a corresponding account number is entered, rather than being typed manually.
Before you start

Ensure that your customer database is organized in a separate worksheet with unique account numbers in the first column and corresponding details (like name and address) in adjacent columns.

Solution 1Recommended

Use the VLOOKUP Function to Retrieve Customer Information

The VLOOKUP function is the most straightforward way to search for an account number and return matching customer data from a specific column in your database.

VLOOKUP searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify. This is ideal for simple invoice auto-filling.

1
Prepare your customer database

Set up your customer records on a separate worksheet (e.g., named 'Sheet1'). Ensure the Account Numbers are in Column A, and the Customer Names are in Column B.

2
Select the destination cell

Go back to your invoice sheet. Click on the cell where you want the Customer Name to appear automatically (for example, cell B2).

3
Enter the VLOOKUP formula

Type the formula =VLOOKUP(A2,Sheet1!A:B,2,0), where A2 is the cell where you type the account number, 'Sheet1!A:B' is your database range, and '2' indicates you want to return the value from the second column.

4
Apply and test

Press Enter. Now, whenever you type a valid account number into cell A2, the customer's name will automatically populate in cell B2. Repeat this for the address by changing the column index number.

Use the VLOOKUP Function to Retrieve Customer Information
Exact Match Requirement: The '0' (or FALSE) at the end of the VLOOKUP formula ensures Excel only returns a result if it finds an exact match for the account number.
Smart Data Processing

Automate Your Invoices Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP, XLOOKUP, and INDEX/MATCH. Build powerful, automated invoices and manage customer databases effortlessly in a highly compatible environment.

  1. 1. Create your workbooks: Open WPS Spreadsheet and create two separate sheets: 'Invoice' and 'Customer Database'.
  2. 2. Input customer data: Populate your database sheet with unique account numbers and their corresponding customer details.
  3. 3. Apply lookup formulas: In your invoice template, select the target cells and input your =VLOOKUP or =XLOOKUP formulas linking back to the database sheet.
  4. 4. Automate your workflow: Simply type an account number into the designated cell on the invoice, and watch WPS Spreadsheet instantly auto-fill all linked customer details.
Fully compatible with Microsoft Excel formulas and file formats.Built-in professional invoice templates available for free.Lightweight software with fast processing for large customer databases.Seamless integration between different worksheets for robust data tracking.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error when auto-filling the invoice?

The #N/A error typically occurs when the exact account number cannot be found in your source database. This might be due to a typo, missing data, or hidden trailing spaces in the cell. You can use the TRIM function to remove accidental spaces.

Can I use XLOOKUP instead of VLOOKUP to fill customer details?

Yes, XLOOKUP is a modern, more flexible alternative available in newer versions of Excel and WPS Spreadsheet. It allows you to search in any direction and handles missing values elegantly. The syntax is simply =XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B).

How do I hide the #N/A error on a blank invoice before an account number is entered?

You can wrap your lookup formula in the IFERROR function. For example: =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,0), ""). This will display a blank cell instead of an error when the account number cell is empty.