LAST POST: Dec 6, 2010  
Applies to What Where used
Excel and other spreadsheets Reference a cell in another sheet

Look-up a price for a stock number
So, if you have a limited budget and or don't want to invest in a fancy inventory and invoicing program, one of the easiest ways to put your computer to work for you is to build an Excel spreadsheet to do this.  The foundation of an application like this one is to use the lookup tables in Excel.  Basically there are two types, Vertical and Horizontal Lookup Tables.  We will use a Vertical Lookup Table for this example.
Step one is to open a new workbook and in the first row, set up a little inventory list...like this one:
Part No. Nominclature Location Units on Hand Cost Retail
A1 Widget Shelf 1a 10 $10.00 $14.00
B2 Thingy Shelf 2a 12 $15.00 $21.00
The critical part of the table is to put the item you will use to do the lookup with on the left most column.  Now, go to page two of your book and in the first cell, type the part no. (a1). You could put the nomenclature in the first column if you wanted to, and do a lookup on the word Widget if you wished to...more on that later. Now, in the second cell (b1) we will enter our formula.  It is very straight forward, but in the way of Microsoft, it's clouded with terms that seem confusing at first. Here is how it should look: =VLOOKUP(A1,Sheet1!A2:F7,6,0)

As in all formulae in Excel, we start with the equal sign (=) to alert Excel we want to use a built in function, in this case VLOOKUP. Then we enter a left bracket to enclose our terms.  The first term is the key item. Since the key item (the one we are using to do the lookup, in this case the part number) is in the first cell of the page we are going to have our simulated invoice in, the address is a1.

Next we enter a comma to separate the terms within the formula.  This comma is followed by the address and the scope of the table. In this case Sheet1!A2:F7.  Sheet1 tells Excel that our lookup table is located on the first sheet of the book. The "!" is a separator. The A2:F7 gives the coordinates for the lookup table...meaning it starts in cell A2 and down to A7, then over to the right from column A to column F.  The A2 location is the upper left corner, and the F7 is the lower right corner of the table.

Next we have another comma separator, followed by the number 6.  This 6 tells Excel that starting from the left most column, go over to the right six columns and there you will find the column wherein lays the answer.  So if you were doing this manually, you would find the lookup term in the first column, then run your finger across six columns (to retail) to find the answer.

Finally, we put a comma in there to separate out the final criteria entry in the formula.  This is either true or false or 1 to 0.  (both will work as 1 means true and zero  means false to Excel). This entry tells Excel if you want an exact match (false means find exact match, true means use fuzzy logic and get close). Use of false means your entry in the search criteria must be exact too. Caps spaces etc are all considered. Then, close the formula with a right parenthesis (bracket).

There is an excellent example and lots of help built right into Excel, and there are even some video aids on the Microsoft support website.  Google Vlookup + Excel.