You finish a project, open your invoice file, and start typing. Client name, billing address, email, payment terms. Then you do it all again for the next client.
Every retyped address is a chance for a typo, and a typo on an invoice can delay payment by weeks. Add up the minutes across a year of billing and you have lost hours you could have spent on paid work.
There is a simple fix. With one Excel formula, VLOOKUP, you type a client ID and the rest of the invoice fills itself in. This tutorial walks you through building that template from scratch, and you can reuse it for every client you ever bill.
The Scenario and Dummy Data
Meet Jordan, a freelance web developer in Austin, Texas. Jordan works with four regular clients and sends about 10 invoices a month.
Jordan keeps a client list on one sheet and the invoice on another. Here is the client data we will use throughout this tutorial:
| Client ID | Client Name | Contact Email | Billing Address | Payment Terms |
|---|---|---|---|---|
| C001 | Lone Star Bakery | [email protected] | 412 Congress Ave, Austin, TX 78701 | Net 15 |
| C002 | Bluebonnet Dental | [email protected] | 88 Lamar Blvd, Austin, TX 78703 | Net 30 |
| C003 | Capital City Realty | [email protected] | 1200 S 1st St, Austin, TX 78704 | Net 30 |
| C004 | Riverwalk Fitness | [email protected] | 305 E 6th St, Austin, TX 78701 | Net 15 |
You can copy this table into your own workbook to follow along. Swap in your real clients once the template works.
Step 1: Set Up the Client List Sheet
Open a new Excel workbook. Rename the first tab to Clients by right-clicking the sheet tab, choosing Rename, and typing the new name.
Type the column headers into row 1, starting in cell A1: Client ID, Client Name, Contact Email, Billing Address, and Payment Terms. Then enter the four client rows from the table above beneath them.
[INSERT SCREENSHOT: The Clients sheet in Excel showing the five column headers in row 1 (Client ID through Payment Terms) and the four dummy client rows filled in below, with the sheet tab named “Clients” visible at the bottom]
Why the Client ID Comes First
VLOOKUP can only search the leftmost column of your data range. That is why Client ID sits in column A.
If you put Client Name first instead, the formula would work, but you would need to type the full name exactly right every time. A short ID like C001 is faster and harder to mistype.
Step 2: Build the Invoice Layout
Click the + icon at the bottom of the window to add a new sheet. Rename it Invoice.
Set up these labels in the top section of the sheet:
- Cell A1:
INVOICE - Cell A3:
Invoice Number, with the value in B3 (for example,1001) - Cell A4:
Invoice Date, with the value in B4 (for example,10/01/2026) - Cell A6:
Client ID, with the value in B6 - Cell A7:
Client Name - Cell A8:
Email - Cell A9:
Billing Address - Cell A10:
Payment Terms
Cells B7 through B10 are where the formulas will go. Cell B6 is the only one you will type into for each new invoice.
[INSERT SCREENSHOT: The Invoice sheet showing the labels in column A (Invoice Number, Invoice Date, Client ID, Client Name, Email, Billing Address, Payment Terms) with cell B6 highlighted in yellow as the input cell]
To format the date, select cell B4, press Ctrl+1 (or Cmd+1 on a Mac), choose Date, and pick the MM/DD/YYYY style.
Step 3: Write the VLOOKUP Formula
Click cell B7 on the Invoice sheet. Type this formula and press Enter:
=VLOOKUP($B$6, Clients!$A$2:$E$5, 2, FALSE)
Nothing will appear yet because B6 is empty. Type C001 into B6 and watch B7 change to “Lone Star Bakery.”
[INSERT SCREENSHOT: Cell B7 on the Invoice sheet selected, with the formula bar showing the full VLOOKUP formula and the cell displaying “Lone Star Bakery” after C001 was typed into B6]
How the Formula Works
Think of VLOOKUP as looking up a name in a phone book. It has four parts:
$B$6is what you are looking for. It is the Client ID you typed.Clients!$A$2:$E$5is where to look. It is your client list, from row 2 to row 5, columns A through E.2is which column to return. Column 2 in that range is Client Name.FALSEtells Excel to find an exact match only. Without it, Excel may guess and hand you the wrong client.
The dollar signs lock the references in place. That way the formula still points to the right cells when you copy it down.
Fill In the Remaining Fields
Only the column number changes for each field. Enter these formulas in the cells below:
| Cell | Field | Formula |
|---|---|---|
| B8 | =VLOOKUP($B$6, Clients!$A$2:$E$5, 3, FALSE) |
|
| B9 | Billing Address | =VLOOKUP($B$6, Clients!$A$2:$E$5, 4, FALSE) |
| B10 | Payment Terms | =VLOOKUP($B$6, Clients!$A$2:$E$5, 5, FALSE) |
Now change B6 to C003. Every field updates to Capital City Realty’s details instantly.
Step 4: Add Line Items and Totals
Below the client section, add a table for the work you did. In row 13, enter these headers: Description, Hours, Rate, and Amount.
Here is sample data for Jordan’s invoice to Lone Star Bakery:
| Description | Hours | Rate | Amount |
|---|---|---|---|
| Homepage redesign | 12 | $85.00 | $1,020.00 |
| Online ordering setup | 8 | $85.00 | $680.00 |
| Site maintenance (September) | 3 | $85.00 | $255.00 |
In the Amount column (D14), enter this formula and copy it down:
=B14*C14
Then, in cell D18, add up the total:
=SUM(D14:D16)
Format the Rate and Amount columns as currency by selecting them and clicking Home > Number Format > Currency. The total for this invoice comes to $1,955.00.
[INSERT SCREENSHOT: The finished invoice showing the auto-filled client section at the top, the three line items in the middle, and the $1,955.00 total at the bottom]
Calculate the Due Date Automatically
You can also have Excel work out the due date from the payment terms. In cell A11, type Due Date. In B11, enter:
=B4+VALUE(MID(B10,5,2))
This pulls the number of days out of text like “Net 15” and adds it to the invoice date. If the invoice date is 10/01/2026 and the terms are Net 15, the due date shows 10/16/2026.
Step 5: Save It as a Reusable Template
Once everything works, clear the Client ID in B6, the invoice number, and the line item rows. Leave every formula in place.
Click File > Save As, and in the file type dropdown choose Excel Template (*.xltx). Next time, open the template, and Excel creates a fresh copy so the original stays clean.
[INSERT SCREENSHOT: The Save As dialog with the “Save as type” dropdown open and Excel Template (*.xltx) highlighted]
To send an invoice, click File > Export > Create PDF/XPS Document. Attach the PDF to your email.
Pro-Tip: Fixing the #N/A Error and Preventing Typos
The most common problem with this setup is the #N/A error. It appears when Excel cannot find the Client ID you typed.
The usual causes are a typo (like C01 instead of C001), a stray space after the ID, or a client you have not added to the list yet. Check the Clients sheet first and confirm the ID matches exactly.
Hide the Error with IFERROR
If you would rather see a friendly message than a cryptic code, wrap the formula like this:
=IFERROR(VLOOKUP($B$6, Clients!$A$2:$E$5, 2, FALSE), "Client not found")
Now a bad ID shows “Client not found” instead of #N/A.
Add a Dropdown to Stop Typos Entirely
Click cell B6, then go to Data > Data Validation. Under Allow, choose List. In the Source box, type =Clients!$A$2:$A$5 and click OK.
[INSERT SCREENSHOT: The Data Validation dialog with “List” selected in the Allow dropdown and =Clients!$A$2:$A$5 entered in the Source box]
Cell B6 now shows a dropdown arrow with your client IDs. You pick one instead of typing it.
Make the Client List Expandable
Our formula only covers rows 2 through 5. When you add a fifth client, the formula will miss them.
To fix this, select your client data and press Ctrl+T to turn it into an Excel Table. Then replace the range in your formulas with the table name, such as Clients[#All]. New clients are included automatically.
Conclusion
You now have an invoice template that fills in client details from a single ID. No more retyping addresses, and far fewer chances for a typo to slow down your payment.
Start with your own client list today. Add your real clients, set your rates, and send your next invoice in a fraction of the time. Once you are comfortable with VLOOKUP, you can use the same technique for price lists, product catalogs, and expense tracking.