You can use the VLOOKUP function to find a
value in one column of a range of related values. In the range, information on any given row provides all the information about that row. The VLOOKUP function vertically searches the leftmost column of the range to find a value you specify. When
the VLOOKUP function finds the value in the leftmost column, it searches across the row for one of the values in that row; you use one of the VLOOKUP function’s arguments to identify the column containing the value you want to find. For example, suppose that you have stored daily rainfall values in your worksheet for each day of the year. You can set up a VLOOKUP function to quickly search the column containing the day numbers and find the rainfall value that corresponds to any of the 365 days that you specify. The VLOOKUP function takes three arguments: First, you identify the cell containing the value for which you want to search. Second, you identify the range containing all of the data that Excel should search. Third, you identify the column containing the information that you want Excel to display.
1. In your worksheet, list the values that Excel
should search in columns.
Note: If necessary, set up a cell to contain the value
for which Excel should search.
2 Click the cell in which you want to store the function.
3 Click the Formulas tab.
4 Click Insert Function. The Insert Function dialog box appears.
5 Type a description here of the function you want to use. This example uses vertical lookup.
6 Click Go.
7 Click VLOOKUP. • A description of the selected function appears here.
8 Click OK. The Function Arguments dialog box appears.
9 Click here and click the cell containing the search value. The selected cell’s address appears in the Lookup_value field.
10 Click here and select the range containing all of the values.
The selected range appears in the Table_array field.
11 Type the number of the column containing the value you want to find.
Note: The leftmost column is 1.
12 Click OK. • Excel displays the result of the formula in the cell you selected in Step 2 as a serial number. • The formula appears in the Formula bar.
TIPS
What does the Range_lookup argument do?
You can use this argument to tell Excel whether to return an exact match or an approximate match to the Lookup_value you specify in Step 9. If you omit the argument or set the argument to TRUE, Excel finds an approximate match. If you set the argument to FALSE, Excel finds only exact matches. In this example, the syntax
for the function that includes a Range_lookup argument would be as
follows: =VLOOKUP(D3,A3:B33,2,FALSE).
Can I use the VLOOKUP function with text instead of values?
Yes, you can. You can search for a text string by typing the text that you want to find in the Lookup_value field. You can also have Excel return text stored in a cell. However, you cannot use the Range_lookup argument to specify whether Excel should find an exact match or an approximate match.
Are there any restrictions concerning the range that I select in Step 10?
Yes. The leftmost range must contain the text or value for which Excel will search. If the leftmost column contains values, each value must be unique, and the entries
must be sorted from lowest to highest. If the leftmost column contains text entries, each entry must be unique, and the entries must be sorted alphabetically.
No Comment