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

arguments follow:

✓
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

column.

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

Figure 14-11:

Using

VLOOKUP to

locate data

in a table.