When a match is found, the function can automatically return the contents of other cells in the same row of the table. For example, a Lookup table may comprise a stock list, with rows of product codes, corresponding prices and descriptions. The VLOOKUP function can search the stock list table for a cell that matches a product code, and return the value of other cells, which contain the product's price and description, in the same row.
This tutorial will deal with one of the important real-world uses of the VLOOKUP function: to automatically generate an invoice from two Lookup tables. In the finished invoice you'll be able to enter both product and customer details with the minimum of keystrokes.As most spreadsheet applications include Lookup functions, these techniques can be applied as easily to Excel as they can to AppleWorks, although Excel has additional spreadsheet formulas that replicate those of Lookup formulas. But while most functions can be shared between AppleWorks spreadsheets and databases, Lookup functions aren't among them.
Although our invoice example involved searching for a specific value - product and client codes - in lookup tables, you don't have to be so precise for the Lookup function to work. You can also enter an optional final parameter, Method, in addition to the standard lookup value, range and offset variables. The Method parameter is used to specify whether an exact match is required, or whether the formula will return the nearest match to the Lookup value. There are three possible values: the first - the default - can be entered when searching a column in ascending order to return the largest value nearest or matching the Lookup value.
Conversely, when the values are listed in descending order, a Method value of -1 will return the smallest value nearest to the Lookup value. If you require an exact match, enter 0. If the Lookup function can't find a matching value, it will return an error.
There is plenty of scope to expand the usefulness of Lookup formulas, particularly when used in conjunction with logical functions, such as IF(), to apply conditional formatting to a spreadsheet.
The IF() function can alter the contents of any cell in a spreadsheet depending on the value of another cell - or the result returned by a Lookup table. A simple illustration is the ability to change invoice headings dynamically, which would be handy in the case of a sole trader operating under more than one guise. If the client codes for customers you deal with as 'Joe Bloggs' are set to begin with 'a', and those for 'Bloggs Design' begin with 'b', a single formula will dynamically alter the contents of a cell to reflect the different status.
If you type:
=IF(LEFT(A1,1)="A","Joe Bloggs","Bloggs Design Incorporated") where cell A1 holds the relevant client code, the formula checks whether the first letter of the code is the letter. A. If it is, it enters 'Joe Bloggs' into the current cell. Otherwise it enters 'Bloggs Design'. The same principle could be used to account for differing VAT rates on products or services, perhaps by adding an extra column to the Lookup table and checking for its value in the invoice spreadsheet.
Remember that if you change details in either of the Lookup tables, completed invoices will also be adjusted if you open them again - not something your accountant will like. The simplest way around this is to save completed invoices in ASCII text format, which strips out formulas from the spreadsheet, but means they can still be opened from AppleWorks later.
VLOOKUP isn't the only Lookup function available in AppleWorks. HLOOKUP (Horizontal Lookup) is an almost identical formula, but rather than searching the leftmost column of a table it looks across top row and returns a value from a cell in a corresponding row. The standard Lookup function is also available: this simply searches for a value in one specified range of cells and returns a corresponding value in a second range.
Click 'Next Page' below for the step-by-step guide...Author: Tom Gorham
Using Lookup tables in AppleWorks