Working with Excel Lookup Functions
The Lookup functions in the Lookup and Reference category of Excel functions are designed to automate the process of matching values in two separate lists or tables in a workbook and then returning a related value. For example, you can set up a price lookup table in a worksheet where you store and update the prices for all the items your company sells. After that, you construct formulas in a sales table using the appropriate lookup function that match an item number entered into a field in the sales table with an item number entered into the price lookup table. When the function finds a match between these item numbers, Excel then copies the price associated with that item number in the price lookup table and pastes it into the appropriate field in the sales table.
The Reference functions are primarily designed to return specific types of information about particular cells or regions of a worksheet. This part of the Lookup and Reference category also includes functions that create hyperlinks to different worksheets and documents and that transpose the data in a table so that data that originally ran across the rows now runs down the columns, and vice versa.
Lookup functions enable you to "look up" values from worksheet ranges. Microsoft Office Excel allows you to perform both vertical lookups (by using the VLOOKUP function) and horizontal lookups (by using the HLOOKUP function). In a vertical lookup, the lookup operation starts in the first column of a worksheet range. In a horizontal lookup, the operation starts in the first row of a worksheet range. Because the majority of formulas using lookup functions involve vertical lookups, we will concentrate on VLOOKUP functions.
In this tutorial, you get a chance to practice creating formulas that automate table lookup, including looking up a single value, either across a row of a lookup data table or down one of its columns, as well as using the Lookup Wizard to perform a lookup that uses two values to find the matching data in a lookup data table.
