VLOOKUP function

VLOOKUP uses a vertical search with reference to adjacent cells.

VLOOKUP checks if a specific value is contained in the first column of an array. The function then returns the value to the same line of a specific array column named by index.

Syntax

VLOOKUP(search criterion, array, index, sort order)

  • search criterion is the value searched for in the first column of the array. The search criterion can also include wildcard characters. To represent any single character, use ? (question mark) in the text that specifies the condition. To represent any number of consecutive characters, use * (asterisk). For example, use B* to match all entries that begin with B, and B??? to match all entries that begin with B and contain three other characters. To represent an actual question mark or asterisk, use ~ (tilde) to precede the ? or *.
  • array is the reference.
  • index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
  • sort order is an optional argument that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, which can be between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise, the function returns the message #N/A.

Example

You want to enter the number of an item on a food menu in cell A1, and have the name of the item be displayed immediately as text in the neighboring cell (B1). The Number to Name assignment is contained in the D1:E100 array. D1 contains 100, E1 contains the name "Vegetable Soup," and so on, for 100 menu items. The numbers in column D are sorted in ascending order; thus, the optional sort order argument is not necessary.

Enter the following formula in B1:

=VLOOKUP(A1, D1:E100, 2)

After you enter a number in A1, B1 shows the corresponding text contained in the second column of reference D1:E100. Entering a nonexistent number causes the text to be displayed with the next number down. If you want a nonexistent number to generate an error message instead, enter FALSE as the last argument in the formula.