How to Automatically Fill Invoice Customer Details with an Account Number in Excel
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.

- 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.
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.
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.
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.
Go back to your invoice sheet. Click on the cell where you want the Customer Name to appear automatically (for example, cell B2).
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.
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 INDEX and MATCH for Flexible Data Retrieval
If your account numbers are not in the first column, or if you need to return multiple values across various columns efficiently, combining INDEX and MATCH is the best approach.
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. Create your workbooks: Open WPS Spreadsheet and create two separate sheets: 'Invoice' and 'Customer Database'.
- 2. Input customer data: Populate your database sheet with unique account numbers and their corresponding customer details.
- 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. 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.

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.




