Excel in Action

Use Excel's INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs

This simple invoicing system allows you to keep a list of products and prices, then populate an invoice with the item and quantity you choose.

(Download the workbook.)

A visitor recently asked how to set up a simple invoicing system in Excel.

This is a common problem in many small businesses, divisions, and sales offices. And it applies to both invoices and purchase orders.

This first figure below shows the general circumstance. In columns B, C, and D, you have key product information. This table could have additional information, including a description, vendor, and so on.

This figure also includes a Scratch column, which I'll explain shortly.

To create an invoice or a purchase order, you first go to the Qty column and erase the previous values.

To do so, you first press F5 or Ctrl+G, or you choose Edit, Go To. Each of these actions launches the Go To dialog box. You select the Qty name or type it into the Reference edit box, then choose OK.

You then enter the quantities to be purchased.

When you recalculate, the Scratch column increments its count each time it finds a non-zero quantity in its row. Then the invoice or purchase order summarizes the information you've marked with your selection.

The summary table -- that is, the purchase order or invoice -- could use at least two different methods to look up information from this data table. I'll explain both techniques.

Set Up the Data Table

After you enter and format the information shown in the table, you need to assign range names. To do so, select the range A2:D13; choose Insert, Name, Create. Make sure that only Top Row is checked; then choose OK.

Also, the VLOOKUP approach will be easier to use if the full data table is named. Select the range A3:D13; choose Insert, Name, Define; then assign the name Data to this range.

Notice in this figure that these range names are anchored, top and bottom, in two shaded rows. When I set up a data table I always anchor my range names in rows like this. By doing so, I know that my names will adjust properly when I insert new rows of data anywhere between those shaded border rows. If I don't use the shaded rows I'm never quite sure whether my range names properly when I enter new rows of data.

The Scratch column increments its value whenever a new quantity is entered. Here's the first formula:

A4:  =IF($D4>0,A3+1,A3)

As you can see, the formula in cell A4 adds the value of 1 to the previous row if a quantity has been entered in cell D4; otherwise, the formula returns the previous value. Copy this formula down the column as shown in the figure.

Use VLOOKUP to Create Purchases Orders and Invoices

The figure below shows the summary table, which represents a purchase order or invoice. It shows extended prices for the four items selected in the first figure.

This particular table has room for ten items. But you could make the table as large as you want.

The nice thing is that the table appears to consist of a variable-length list. No matter how many items are chosen within the limits set, the remainder of the table appears to be empty.

Here are the key formulas for this table:

A3:   1
A4:   =A3+1

Cell A4 increments the count begun in cell A3. Copy cell A4 down the column as needed.

B3:   =VLOOKUP($A3,Data,2,FALSE)

This formula returns the product name for the item number that appears in cell A3. If that item number isn't found, the formula returns #N/A.

Here's the general form for this function:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value...The current line number, of course.
   
• table_array...The Data range assigned above.
   
• col_index_num...Because Product is in the second column of the Data range, we use the value 2.
   
• [range_lookup]...This is an optional parameter that uses TRUE as its default. But if we enter FALSE as its value the function acquires two useful characteristics. First, it returns #N/A if the lookup_value isn't found. Second, it returns data associated with the first lookup_value found.

C3:   =IF(ISERROR($B3),"",$B3)

If cell B3 has an error value, this formula returns a null string (quote, quote), which effectively hides the formula from view.

To be clear, the range C7:F12 contains formulas that return null strings. This is why that area appears to be empty.

D3:   =IF(ISERROR($B3),"",VLOOKUP($A3,Data,3,FALSE))

If cell B3 doesn't contain an error, this formula looks up the line number and returns the data for the third row of the Data table, which is the price.

E3:   =IF(ISERROR($B3),"",VLOOKUP($A3,Data,4,FALSE))

If cell B3 doesn't contain an error, this formula returns the quantity.

F3:   =IF(ISERROR($B3),"",$E3*$D3)

If cell B3 doesn't contain an error, this formula returns the total cost for the product in its row.

After you enter these formulas, copy the range B3:F3 down the column as needed.

F13:   =SUM(F2:F12)

This formula merely returns the sum of the extended values in column F. It treats text characters as zero.

To complete this table, define the print area. To do so, first select the area to be printed, then choose File, Print Area, Set Print Area.

One disadvantage to using VLOOKUP is that we need to specify which column number to return data from. Therefore, if we rearrange the table, the VLOOKUP function could return data from the wrong column. There are ways to eliminate this problem, but it creates a more complex formula. Instead, let's look at the INDEX-MATCH approach.

Use INDEX-MATCH to Create Purchases Orders and Invoices

The INDEX-MATCH functionality probably is the most powerful and flexible lookup method that Excel offers. You can see it illustrated in the following figure.

A3:   1
A4:   =A3+1

Copy cell A4 down the column as needed.

B3:   =MATCH($A3,Scratch,0)

MATCH looks up the specified data in a row or column and returns the index number for the item found. For example, if MATCH identifies the 23rd item in the list, it returns the value 23.

Here's the general form of this function:

=MATCH(lookup_value, lookup_array, match_type)

• lookup_value...The value you're searching for.
   
• lookup_array...Typically, a single row or column to search through.
   
• match_type...This can be the value -1, 0, or 1. Each value causes MATCH to behave differently. You will use 0 most of time. When you do, the lookup_array doesn't need to be sorted; MATCH returns the index for the first instance found; and MATCH returns #N/A if it doesn't find an exact match. That is exactly the performance we need here.

C3:   =IF(ISERROR($B3),"",INDEX(Product,$B3))
D3:   =IF(ISERROR($B3),"",INDEX(Price,$B3))
E3:   =IF(ISERROR($B3),"",INDEX(Qty,$B3))

These functions have an identical format. If the MATCH formula in cell B3 returns an error, return a null string; otherwise, return the value specified by the MATCH function from the Product, Price, or Qty columns.

F3:   =IF(ISERROR($B3),"",$E3*$D3)

This formula is identical to the formula shown for cell F3 above. If cell B3 doesn't contain an error, this formula returns the total cost for the product in its row.

As before, copy the range B3:F3 down the column as needed. And then define the print area.

(Download the workbook.)