Excel's lookup, reference, and information functions are quite a bit different than the mathematical functions discussed in the last few chapters. Most math functions take your data as a starting point and use it to calculate some new result. Lookup, reference, and information functions, on the other hand, don't generate any new data. Instead, they let you search for and extract important bits of content from your worksheet and then reuse it in ways guaranteed to delight spreadsheet lovers worldwide.
All three types of functions play different but complementary roles. A lookup function finds and then copies data from a particular cell in a worksheet. A reference function retrieves more general information about groups of cells (like how many columns ' worth of data is in your table). It's common to use lookup and reference functions together. You might use a reference function to find out which cell has the data you want to use, and a lookup function to actually retrieve the cell's contents.
Finally, information functions let you determine what kind of content resides in an individual cell (for instance, a number or text). Knowing that can help you construct extremely powerful conditional formulas , which behave differently depending on the type of data they encounter.
All these functions might not immediately seem useful, but they become indispensable in a variety of situations. Here are a few common examples:
Tip: Excel user groups and online bulletin boards are full of problems that you can solve only with lookup functions. Try, for instance, the Microsoft Office Discussion groups at www.microsoft.com/office/community/en-us/ or third-party discussion groups like www.excelforums.com.
In this chapter, you'll learn how to build basic lookup functions, as well as how to overcome some of their limitations. You'll also learn how to use Excel's Lookup Wizard, which helps you construct complex lookup formulas by asking you a series of questions. After a brief introduction to information functions, you'll end up by taking a look at a practical example that uses lookup functions to link together two worksheets. In this example, the first worksheet provides a complete product catalog, and the second worksheet provides a template for an invoice. With the help of a few crafty lookup functions, you'll learn how to quickly build a complete invoice based on this linked product catalog.