Microsoft Office Tutorials and References

In Depth Information

**Syntax**

55. In the lookup table, the rep name is in column 2 of the table, so type 2

to specify that you want to return the second column of the lookup

table.

66. Finish the function with FALSE)

FALSE). Press Ctrl+Enter to accept the for-

mula and keep the cursor in cell D2.

77. Double-click the fill handle to copy the formula down to all the

rows.

88. VLOOKUP is a very time-intensive calculation. Having thousands of

VLOOKUP formulas significantly affects your recalculation times.

In this particular case, you have successfully added rep names. It

would be appropriate to convert these live formulas to their current

values. Therefore, press Ctrl+C to copy. Then, from the Home tab, se-

lect Paste, Paste Values to convert the formulas to values.

99. Look through the results. If a sale was credited to a new rep who is

not in the table, the name appears as #N/A. Manually fix these re-

cords, if needed.

Note

If your lookup table is arranged with the key field in row 1, you

should use HLOOKUP, which is discussed later in this chapter. If

your data is vertical but the key field is not the leftmost column, you

can use a combination of INDEX and MATCH, also explained later in

this chapter.

To recap, the two versions of the VLOOKUP formula behave very differ-

ently. VLOOKUP with FALSE as the fourth parameter looks for an exact

match, whereas VLOOKUP with TRUE as the fourth parameter looks for the

closest (lower) match. In the TRUE version, the lookup table must be sorted.

In the FALSE version, the table can be in any sequence. In every case, the key

field must be in the left column of the lookup table.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)