# VLOOKUP function

This article describes the formula syntax and usage of the **VLOOKUP** function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

## Description

You can use the **VLOOKUP** function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.

If you know the employee's ID number, you can use the **VLOOKUP** function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula **=VLOOKUP(38, A2:C10, 3, FALSE)**. This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado").

The V in **VLOOKUP** stands for vertical. Use **VLOOKUP** instead of **HLOOKUP** when your comparison values are located in a column to the left of the data that you want to find.

## Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

**lookup_value**Required. The value to search in the first column of the table or range. Theargument can be a value or a reference. If the value you supply for the l*lookup_value*argument is smaller than the smallest value in the first column of the*ookup_value*argument,*table_array***VLOOKUP**returns the #N/A error value.**table_array**Required. The range of cells that contains the data. You can use a reference to a range (for example,**A2:D8**), or a range name. The values in the first column ofare the values searched by*table_array*. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.*lookup_value***col_index_num**Required. The column number in theargument from which the matching value must be returned. A*table_array*argument of 1 returns the value in the first column in*col_index_num*; a*table_array*of 2 returns the value in the second column in*col_index_num*, and so on.*table_array*

If the ** col_index_num** argument is:

- Less than 1,
**VLOOKUP**returns the #VALUE! error value. - Greater than the number of columns in
,*table_array***VLOOKUP**returns the #REF! error value.

**range_lookup**Optional. A logical value that specifies whether you want**VLOOKUP**to find an exact match or an approximate match:- If
is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than*range_lookup*is returned.*lookup_value*

** Important ** If ** range_lookup** is either TRUE or is omitted, the values in the first column of

**must be placed in ascending sort order; otherwise,**

*table_array***VLOOKUP**might not return the correct value.

For more information, see Sort data in a range or table.

If ** range_lookup** is FALSE, the values in the first column of

**do not need to be sorted.**

*table_array*- If the
argument is FALSE,*range_lookup***VLOOKUP**will find only an exact match. If there are two or more values in the first column ofthat match the*table_array*, the first value found is used. If an exact match is not found, the error value #N/A is returned.*lookup_value*

## Remarks

- When searching text values in the first column of
, ensure that the data in the first column of*table_array*does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases,*table_array***VLOOKUP**might return an incorrect or unexpected value.

For more information, see CLEAN function and TRIM function.

- When searching number or date values, ensure that the data in the first column of
is not stored as text values. In this case,*table_array***VLOOKUP**might return an incorrect or unexpected value. - If range_lookup is FALSE and
is text, you can use the wildcard characters — the question mark (?) and asterisk (*) — in*lookup_value*. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (*lookup_value***~**) preceding the character.

## Example

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.