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.
by Charley Kyd, MBA Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
|
(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.
This figure is virtually identical to the previous figure. The only
difference is that column B contains an index number rather than a
product name. Let's take a closer look at its formulas.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.)
|