Chapter 11. Lookup, Reference, and Information Functions


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:

  • You want to allow viewers of your spreadsheet to look up items and then see these items used in a series of calculations. For example, you've got a worksheet with a long list of customer records (each of which includes info like their mailing address and how much money they owe your company). You not only want to make the spreadsheet searchable, but you also want to present your searchers with a series of calculations about the items they've found, such as how much money is owed to your company after 30 days plus interest, 60 days plus interest and penalty, and so on. Lookup and reference functions let you turn your spreadsheets into extremely useful, interactive databases. It's kind of like having an accountant -in-waiting for anyone who wants to ask questions about the information in your spreadsheet.

  • You want to extract some data from a table and use it in another worksheet. For example, you have a list of mailing addresses, and you need to extract a specific address and place it at the top of a form letter.

  • Your worksheet has more than one table, and they're all designed to work together. For example, you want to pluck out a product's name and price from a massive table of product information and use it to create an invoice. This represents one of the most powerful ways you can use lookup functions.


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.



Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net