VLOOKUP( ) and HLOOKUP( ) work well for linking together tables in a worksheet. They also impose a few restrictions, however, including:
The lookup column must 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. There's no way to 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.
There are certain lookup rules you simply can't get around. For example, the lookup functions aren't much use if there are 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, you should probably opt for Excel's list feature (Section 13.1), 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, it can be 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 ...