Excel 2003 provides two lookup functions that you can use, to quickly retrieve information from a table. The functions are called HLOOKUP (horizontal lookup), and VLOOKUP (vertical lookup). They are similar to database functions in the sense that they retrieve values based on reference data. They are, however, different from database functions in the way they operate.

The VLOOKUP function will look in the leftmost column of a table for a value you specify. When it finds the value you specified, it will return a value that is located in the same row, a specified number of columns into the table. It is called VLOOKUP because it looks vertically down a column for a match, and then retrieves data from across the row.

HLOOKUP is similar, but it will look horizontally across the upper row of your table, and then retrieve data from down a column. To get a clearer picture of how lookup functions work, study the following example.

Here we have a table of ticket prices for flights to different countries. To simplify matters, the data range for the table, has been given a defined name, Price that can be used in functions and formulas. If we activate a cell (F1) and enter =VLOOKUP(“England”, Price,2) into the formula bar, cell F1 will show the value 550.

The lookup function looked vertically down the leftmost column of the lookup table (Price) until it found a match for the text string “England”. The function then returned the value that is in the second column of the table, from the row where the match was found.

The arguments for the lookup function are VLOOKUP(value to match, lookup table name or range, column number in the table to extract data from, true or false). The last argument will default to true if it is omitted. If the last argument is true, VLOOKUP will search for the closest match. If it is false, VLOOKUP will search for an exact match.

HLOOKUP is the same as VLOOKUP, except that it looks across rows for a match rather than down columns. To use HLOOKUP, the lookup table would be arranged in this way.

Since Excel is designed with more cells in the vertical direction than in the horizontal direction, and because vertical table design is more intuitive for most people, VLOOKUP is generally used more often than HLOOKUP.