Understanding Lookup Tables

The table-more properly referred to as a lookup table-is the key to performing lookup operations in Excel. The most straightforward lookup table structure is one that consists of two columns (or two rows):

• Lookup column This column contains the values that you look up. For example, if you were constructing a lookup table for a dictionary, this column would contain the words.
• Data column This column contains the data associated with each lookup value. In the dictionary example, this column would contain the definitions.

In most lookup operations, you supply a value that the function locates in the designated lookup column. It then retrieves the corresponding value in the data column.

The lookup table can be one of these:

• A single column (or a single row). In this case, the lookup operation consists of finding the nth value in the column.
• A range with multiple data columns. For example, in the dictionary example, you might have a second column for each word's part of speech (noun, verb, and so on), and perhaps a third column for its pronunciation. In this case, the lookup operation must also specify which of the data columns contains the value required.
• An array. In this case, the table does not exist on a worksheet but is either an array of literal values or the result of a function that returns an array. The lookup operation finds a particular position within the array and returns the data value at that position.