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.
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.
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. ...