A Business Invoice, Part Three

A Business Invoice, Part Three

In the previous section, you pulled sales details about an invoice. In this section, you'll see how to get the customer details and add them to your invoice.

The problem we face is that the customer data is on a separate worksheet to the sales data. The sales data worksheet is where we have the invoice number. The question then is how to associate an invoice number with the customer's details. The answer is the Customer Number. We have this on both the Customer_Data and theSales_Data worksheets. We need to select a customer's details where the Customer Number matches on both worksheets.

As an example, take the invoice number 12340 on the Sales_Data worksheet. In the next column, we have a Customer Number, which is customer 0001:

This Customer Number is also on the Customer_Data worksheet:

We can use nested VLOOKUPs to pull data from both worksheets where the customer number matches, and then insert just the customer details into the Invoice worksheet.

Nested VLOOKUPs

You can nest one VLOOKUP function inside of another. The technique can be quite tricky to understand, so you may need to re-read this a couple of times!

Let's do the Phone number and email address first, as we'll use CONCATENATE as well as nested VLOOKUPs in the name and address fields, which will add another layer of complexity.

Click inside of cell D7 of your Invoice worksheet. Now click inside of the formula bar at the top of Excel and enter the following: (You can enter yours on one line. You can just copy and paste the formula below)

=VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 8)

Press the enter key on your keyboard and you should see a phone number appear in cell D7. This phone number is coming from the Customer_Data worksheet. But how does it work?

First, have a look at what's needed for a single VLOOKUP again:

VLOOKUP( value_to_lookup, data_to_search, results_column)

The first item between the round brackets is value_to_lookup. Previously, we just entered a cell reference here. This cell reference was B5, which was the invoice number. This time, however, we don't need the invoice number from the Sales_Data worksheet. We need the Customer Number. This is in the cell next to the invoice number. Our inner VLOOKUP is designed to get this Customer Number. Here it is:

VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2)

Again, we used B5 as the value to look up. This is the invoice number. The data to search is in the cells A2 to B5 of the Sales_Data worksheet. The result column is column 2, which is the Customer Number.

When this nested VLOOKUP has returned the Customer Number it is used with the outer VLOOKUP:

=VLOOKUP(RETURNED_CUSTOMER_NUMBER, Customer_Data!A2:Customer_Data!I5, 8)

The RETURNED_CUSTOMER_NUMBER above is the result from the inner VLOOKUP. This is then used to search the Customer_Data in cells A2 to I5 of the Customer_Data worksheet. The results column at the end is column 8, which contains the phone number.

Now click inside of cell D8 of your Invoice worksheet. Enter this nested VLOOKUP:

=VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 9)

The only difference here is the column number at the end, column 9. This is where we have stored the email address on the Customer_Data worksheet.

Press the enter key on your keyboard and you should see an email address appear in cell D8.

Now that we have an email address and phone number, we can get the customer's name and address. We could have separate cells here: a cell for the first name, a cell for the surname, a cell for the first line of the address, a cell for the next line of the address, and a separate cell for the zipcode/postcode. What we have done, however, is to have one cell for the customer's full name, and one cell for the customer's address. The customer's full name is in cell B7:

And the customer's address is in cell B8:

If you have a look at the customer name you'll see it's in the format LAST_NAME, FIRST_NAME, with a comma separating the two:

Doe, Jane

In order to get this we'll need two nested VLOOKUPs, one for the last name and one for the first name. In order to join the two together, and add a comma, we can use the inbuilt function CONCATENATE. The CONCATENATE function looks like this:

CONCATENATE( TEXT_ITEM_1, TEXT_ ITEM_2, TEXT_ ITEM_3 …)

You can have up to 255 Text items. A comma is used to separate each Text item. If you actually want a comma you need to treat it as a Text item. For example, here's our Doe, Jane text using CONCATENATE:

=CONCATENATE("Doe", " ,", "Jane")

So Text_1 is "Doe", Text_2 is "," and Text_3 is "Jane".

With this in mind, here's our nested VLOOKUP with CONCATENATE (you can copy and paste this):

=CONCATENATE(VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 3), ", ", VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 2) )

This is a very long and messy formula. So let's break it down.

=CONCATENATE(

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 3),

", ",

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 2)

)

The first VLOOKUP gets the surname from the Customer_Data worksheet. This goes as the first Text Item of CONCATENATE. To put the comma in we have ",". This is the second Text Item of CONCATENATE. The third Text item is the other VLOOKUP. This gets the first name from the Customer_Data worksheet.

So click inside cell B7 on your Invoice worksheet. Click into the formula bar and enter (or copy and paste) the CONCATENATE code above. When you press the enter key on your keyboard you should have the surname and first name just like ours.

The address uses the same technique, but the CONCATENATE formula is even longer. (It's only longer because we need the address lines and the zipcode/postcode. Here it is to copy and paste into cell B8 on your Invoice worksheet:

=CONCATENATE(VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 4), " , ",
VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 5), " , ",
VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 6), " , ",
VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 7))

It looks insanely complicated, but it's just a longer version of the first CONCATENATE. The only difference is that we have more VLOOKUPs and more commas to insert.

Once you've added the new formula, you should have an address in cell B8 of your worksheet.

And that's it! Try it out. Enter a new invoice number into cell B5, one of your invoice numbers from the Sales_Data worksheet. When you press enter, you should see the invoice automatically update itself.

You can format your invoice however you like. Once formatted, the invoice can be printed out and sent to a customer.

One final word on invoices. If you click File > New you should find that Excel comes with some invoices templates. Most of these templates use the VLOOKUP techniques you have explored in these lessons. Try them out and you should find that you have a better understanding of how the invoice templates work.

In the next section, you'll learn how to create an Excel template.

Posted in: Microsoft Excel,Microsoft Excel 7 (Processing Data in Excel),Tips,Trick,Tutorial,Tutorial Excel 7