Microsoft Office Tutorials and References
In Depth Information
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
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
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.
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
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.