O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Advanced Lookups

VLOOKUP() and HLOOKUP() work well for linking together tables in a worksheet. They also impose a few restrictions, however, including:

  • The lookup column has to be the first column or row in the range. That means you can't use VLOOKUP() to retrieve columns to the left of the lookup column, and you can't use HLOOKUP() to retrieve rows above the lookup row.

  • You must choose between horizontal and vertical lookups. You can't perform a lookup that finds cells based on a lookup column and a lookup row.

In this section, you'll learn how to skirt these restrictions with the help of other functions.

Tip

You simply can't get around certain lookup rules. The lookup functions aren't much use if you have potentially multiple matches. That means you can't use a lookup function to retrieve your top-10 selling products, for example. If you want to use this sort of logic, then you should probably opt for Excel's list feature (Chapter 14), which provides filtering capabilities.

MATCH(): Finding the Position of Items in a Range

The MATCH() function lets you find the position of an item in a range. On its own, MATCH() doesn't accomplish a whole lot, but when used in conjunction with some of the functions described later in this section, you'll find it's really handy. Here are some MATCH() fundamentals.

To use MATCH(), you simply specify the search value (either a number or text) and the range you want to search:

MATCH(search_for, range, [match_type])

The range you use must be one-dimensional. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required