Excel 2010: The Missing Manual

Chapter 12. Lookup, Reference, and Information Functions

Excel’s lookup, reference, and information functions are quite a bit different from 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 can 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 may not immediately seem useful, but they become indispensable in a variety of situations. Consider a few common examples:

• You want to let spreadsheet ...