Introduction to Microcomputers



Computer Data Analysis Instructor: Greg Shaw

CGS 2518

The VLOOKUP Function

I. Looking Up a Value in a Table

The VLOOKUP function searches a table of data in a spreadsheet for a specific value. When the value is located, a related value is retrieved from the table and stored in the cell.

For example, a professor could use VLOOKUP to find a student’s numeric grade average in a table, and have the corresponding letter grade stored in the cell.

II. Arguments

VLOOKUP requires 3 arguments:

1. The value to look up in the table (a cell reference)

2. The range of cells that contains the table in which the value is to be looked up

3. The column number within the table that contains the possible results (i.e., the values to be stored in the cell)

III. Example

Suppose a 2-column table is stored in range I20 : J24 of a spreadsheet, as shown below, and that a student’s numeric average is stored in cell I4.

| |I |J |

|20 |0 |F |

|21 |60 |D |

|22 |70 |C |

|23 |80 |B |

|24 |90 |A |

Also suppose that a cell contains this function:

=VLOOKUP(I4, $I$20:$J$24, 2)

Then, a letter grade from the 2nd column (column J) will be stored in the cell containing the function.

Which letter grade? It depends where the numeric grade would go in the scale of values in the first column (column I).

IV. How It Works

The first column of the table is scanned from top to bottom until the largest value is found that is not greater than the value being sought. The corresponding entry in the second column is then stored in the cell.

For example, suppose the numeric grade in I4 is 88. Then, since row 23 has the largest value that is not greater than 88 (i.e., 80), the value in the second column of row 23 (a “B”) is stored in the cell!

← Note that the values in the first column of the table must be stored in ascending order, or the function will not work!

V. How to Create a VLOOKUP Table for a “Range” Lookup

1. The table will occupy two columns. The leftmost column will have the values to be “looked up” and the rightmost will have the related values to be stored in the cell.

2. The values in the leftmost column must be entered in ascending order, from the top of the column to the bottom

3. In each row of the table, the left cell (i.e. first column) will have the starting value (i.e. the lowest value) for the range, and the right cell (second column) will have the related value

← In the example above, the first row contains 0 and F because 0 is the lowest value in the “F” range. And the 5th row contains 90 and A, because 90 is the lowest value in the “A” range.

VI. “Exact Match” Lookups - The Optional 4th Argument

The example above and my “Gradebook” workbook both show how to look up a value located in one of several ranges of values. But suppose you want to find an exact match (not a range) for the value you are looking up?

To search for an exact match, we must use the optional 4th argument of the VLOOKUP function (“range lookup”) and set it to false. When this argument is omitted - as in the previous examples - Excel uses the default value of true, which specifies a range lookup.

VII. How to Create a VLOOKUP Table for an “Exact Match” Lookup

• This is the same as for a range lookup, except that each cell in the left column contains the exact value to be “looked up” instead of the lowest value in the particular range

• As with a rage lookup, the values in the left column must appear ascending order, from the top of the column to the bottom

VIII. The HLOOKUP Function (Optional)

VLOOKUP (the “V” stands for vertical) has a first cousin named HLOOKUP, which works the same way but the data in the table are arranged horizontally, by rows instead of by columns.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download