iWork '09: The Missing Manual

Looking Up and Fetching Cell Data

The data-matching functions you saw in the last section—COUNTIF, SUMIF, AVERAGEIF, and their variations—are terrific at working with data in groups and tallying up matches in the aggregate. But because their job is to find a match, work it into a calculation, then zip along to the next match, they’re all about the bottom line. They can’t share the content or location of individual cell values. That’s perfect for summarizing many matching items, but when you want to fetch information related to a single unique table entry (a product, a student, an interest rate, a secret fortress listing), you have to look elsewhere. Specifically, you have to look up.

Lookup functions find data from a specific cell in your spreadsheet based on its content. From there, you can branch out to pluck content from nearby cells. This means you can, on the fly:

• Look up a product by its part number and let Numbers plug its product name and price into a packing list for you automatically.

• Fetch the letter grade that corresponds to a student’s percentage score and add it to your grade book.

• Type a company name into your invoice to have Numbers retrieve its address from a table of contact info, plugging it into the invoice for you.

The common feature among these examples is that all involve a prestocked table of reference data (a product catalog, the grade scale, company contact info) where every item, grade level, or company is listed just once. You can then use that storehouse ...