Function Problems

How to Auto-Fill Customer Addresses with an Invoice Drop-Down in Excel

Phi Hung VoPhi Hung Vo Nov 10, 2026 870 views

Question details

The user wants to create a dynamic drop-down list in an invoice workbook that automatically populates the customer's name and address when selected, and updates automatically when new customers are added.

How to Auto-Fill Customer Addresses with an Invoice Drop-Down
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Creating an automated invoice template where selecting a customer from a drop-down list auto-fills their billing details.
Observed behavior
The user needs a solution combining data validation and lookup formulas linked to a dynamic table to achieve automated data entry.
Before you start

Ensure you have a dedicated worksheet containing your customer database with columns for Customer Name, Address, City, and other relevant billing details.

Solution 1Recommended

Use Data Validation and VLOOKUP with an Excel Table

By formatting your customer list as a Table, the drop-down list will automatically expand when new customers are added. VLOOKUP formulas will then fetch the corresponding address.

Using an official Excel Table for your source data is the key to making your drop-down list dynamic. When you add a new customer to the bottom of a Table, the data validation range expands automatically without needing manual adjustments.

1
Format Customer List as a Table

Go to your customer database sheet, select the entire range of your customer data (including headers), navigate to the 'Home' tab, and click 'Format as Table'. Choose a style and confirm that your table has headers.

2
Create the Drop-Down List

Go to your Invoice sheet and select the cell where you want the customer name drop-down to appear. Click the 'Data' tab, select 'Data Validation', set the 'Allow' criteria to 'List', and select the customer name column from your newly created table as the source.

3
Apply VLOOKUP for Address Fields

In the address cells below the drop-down, enter a VLOOKUP formula to pull the address based on the selected customer name. For example, use =VLOOKUP(B5, CustomersTable, 2, FALSE) where B5 is your drop-down cell, CustomersTable is your data table, and 2 is the column index for the address.

Use Data Validation and VLOOKUP with an Excel Table
Dynamic Updates: Because the source data is formatted as a Table, any new customer added to the bottom of the list will instantly appear in your invoice drop-down.

Automate Your Invoices Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful Data Validation and VLOOKUP functions that are fully compatible with Excel, making it incredibly easy to build automated, dynamic invoice templates.

  1. 1. Open your Workbook: Launch WPS Spreadsheet and open your invoice template.
  2. 2. Set up Data Validation: Navigate to the Data tab, click 'Validation', and set your customer list as the source.
  3. 3. Insert Lookup Formulas: Use the built-in VLOOKUP function to automatically fetch and display the customer's address.
Fully compatible with Microsoft Excel formulas, tables, and data validation features.Intuitive Data Validation tools for creating dynamic drop-down lists effortlessly.Free, lightweight, and fast performance for handling large customer databases.
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my drop-down list updating when I add new customers?

If your drop-down list doesn't update automatically, your source data might not be formatted as a Table. Select your customer data and press Ctrl+T (or go to Home > Format as Table) to convert it into a dynamic table.

Can I use XLOOKUP instead of VLOOKUP for the invoice address?

Yes, if you are using a newer version of Excel or WPS Office that supports XLOOKUP, you can use it to fetch the address. XLOOKUP is more flexible as it doesn't require the lookup value to be in the first column.

How do I hide the #N/A error when no customer is selected?

You can wrap your VLOOKUP formula in an IFERROR function. For example, use =IFERROR(VLOOKUP(...), "") to display a blank cell instead of an error when the drop-down is empty.