Lesson 13

Preview

VLOOKUP & HLOOKUP Functions

​Practice Exercise 11: Use a VLOOKUP table for applying exact matching data

STEP 1: Continue to use 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

TEP 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  (FIGURE 3).

Figure 3

STEP 3C: Highlight the range with mouse. Press F4 key. Dollar sign symbols appear for the column and row addresses; $G$1:$H$6 (FIGURE 4).

Figure 4

STEP 4A: Enter a comma after “$H$6” (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 “2” (FIGURE 5).

​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

CONSIDER IF YOU ARE DOING AN APPROXIMATE MATCH OR EXACT MATCH BEFORE DECIDING WHETHER TO LEAVE THE 4TH ARGUMENT OUT.

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 (FIGURE 8).

​You should see all Category Scores in Column D, from 50 to 99.

Figure 8

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 LESSON


Back to: Intro to Excel 2016 Data Analysis > Module 4 – Intro to Excel 2016 Data Analysis

Leave a Reply