Practice Exercise 11: Use a VLOOKUP table for applying exact matching data
STEP 1: Using data file, “FUNCTIONS.xlsx” select the tab, VLOOKUP#1 EXACT MATCH (FIGURE 1).
FIGURE 1
|
STEP 2A: (FIGURE 2) Select cell address D2, enter: =VLOOKUP(
STEP 2B: For the 1st argument, lookup_value, we can enter the data value itself or the cell address. In this case, we enter the cell address of the value, by clicking on cell address C2 with the mouse (FIGURE 2). STEP 2C: Cell address C2 appears as 1st argument (FIGURE 2). |
 FIGURE 2
|
|
STEP 3A: Enter a comma after “C2” (FIGURE 3).
STEP 3B: For the 2nd argument, table_array, enter the range of the VLOOKUP table, G1:H6 (top graphic). We can enter the range by clicking on cell address H1 with the mouse and dragging mouse to cell address I18 (FIGURE 3). |
 FIGURE 3
|
|
STEP 3C: Highlight range with mouse. Press F4 key. Dollar sign symbols should appear for the column and row addresses; $H$1:$I$17 (FIGURE 4)..
|
 FIGURE 4
|
|
STEP 4A: Enter a comma after “C2” (FIGURE 5).
STEP 4B: For the 3rd argument, col_index_num, we will enter 2, since our lookup values are in the 2nd column of the lookup table (the left-most column of a lookup table is always column 1) (FIGURE 5). |
 FIGURE 5
|
|
STEP 5A: Enter a comma after “C2” (FIGURE 6).
STEP 5B: For the 4th argument, [range_lookup], understand that this is an optional argument. Since we are doing an exact match, we must enter 0 or FALSE, followed by a closed parenthesis (FIGURE 6). Note: The default is 1 or TRUE if we do not enter this 4th argument. Failure to enter this 4th argument can result in errors. |
 FIGURE 6
|
|
STEP 6: We are ready to execute. Press Enter key. The result in cell address D2 should be 70 (FIGURE 7).
|
 FIGURE 7
|
|
STEP 7: Move mouse up to the bottom-right corner of cell address D2, and when you see the Auto-Fill symbol (thin black cross), double-click the mouse and the cells in column D will populate. Check them for accuracy.
|
You should see all Category Scores in Column D, from 50 to 99.
|
STEP 8: Although HLOOKUP is not used nearly as much as VLOOKUP, if we were to use it, the HLOOKUP table top row would be used in the same way as our VLOOKUP left column, or 1st Column. The HLOOKUP table bottom row would be used in the same way as our VLOOKUP right column, or 2nd Column.
END OF PRACTICE EXERCISE 11