Microsoft Office Tutorials and References
In Depth Information
Looking It Up
✓ The first argument: Tells the function to search for Monday in the first
row of the table.
✓ The second argument: The table itself is specified by its assigned name.
✓ The third argument: Tells the function to return the data in the second
row of the specified column. While this table has just two rows, there is
no effective size limit to the table you use with HLOOKUP.
✓ The fourth argument: Specifies that an exact match for Monday must
be found. If you set this argument to true or omit it, HLOOKUP finds an
approximate match. For approximate matching to work properly, the
values in the row must be sorted, left to right, in ascending order.
VLOOKUP works in the same way, except that it finds a value in the first
column of the table and then moves over a specified number of columns. The
✓ The value to find in the leftmost column of the table.
✓ The address of the table itself: This is either a range or a named area.
✓ The column offset from the leftmost column: This is not a fixed column
number but rather the number of columns relative from the leftmost
✓ A true or false value: If true (or omitted), VLOOKUP finds an
approximate match. If false, an exact match is required. For an approximate
match, the column must be sorted in ascending order.
Figure 14-11 shows an example of using VLOOKUP. The worksheet displays
products and annual revenue data for the fictitious guitar shop. The range
A6:D27 has been named Sales.
in a table.