Find Data in Google Spreadsheets with VLOOKUP

The Google Sheets function VLOOKUP, which stands for "vertical lookup," can be used to find specific information in a table of data or a database. It's even possible to return approximate matches if an exact match cannot be found.

Instructions in this article apply to the web version of Google Sheets. All steps are the same regardless of which browser you use.

VLOOKUP Function Syntax and Arguments in Google Sheets

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the VLOOKUP function is:

= VLOOKUP(search_key, range, index, is_sorted)

The arguments are as follows:

search_key (required): The row or record of the data table to be searched for.
range (required): The number of columns and rows that VLOOKUP should search.
index (required): The column number of the value you want to find.
is_sorted (optional): Use the boolean values TRUE or FALSE to indicate whether or not the range is sorted in ascending order.

The VLOOKUP function looks for the search_key in the first column of the data table. It then locates and returns the information you seek from another field of the same record using the supplied index number. VLOOKUP normally returns a single field of data as its output.

Although not always required, it is usually best to first sort the range of data that VLOOKUP is searching in ascending order using the first column of the range for the sort key. If the data is not sorted, VLOOKUP might return an incorrect result.

The is_sorted argument must be set to TRUE to return approximate matches. If omitted, the is_sorted value is set to TRUE by default.

How to Enter the VLOOKUP Function

The example below uses the following formula to find the discount for quantities of goods purchased:

=VLOOKUP(A2,A5:B8,2,TRUE)

To enter the VLOOKUP function manualy:

Select the cell where you want the results of the VLOOKUP function to appear to make it the active cell.
Type =VLOOKUP( in the Fx field at the top of the spreadsheet.

As you type, the auto-suggest box will recommend the =VLOOKUP function. Select it to start entering arguments.
Select the cell containing the value you want to make the search_key argument.
Type a comma (,) to act as a separator, then highlight the cells to be included in the range.

Table headings are not included in the range.
Type another comma, then type the number of the index column.
Type another comma, then type TRUE.
Press Enter on the keyboard to add a closing bracket and complete the function.

The result appears in the cell where the function was entered. When you select the cell, the complete function appears in the formula bar above the worksheet.

In the example, the Quantity column does not contain an exact match for the search_key value of 23. Since the is_sorted argument is set to TRUE, VLOOKUP found an approximate match, or the nearest value in size that is smaller than 23. Therefore, VLOOKUP looked for the discount percent in the row containing 21 and returned a discount rate of 2.5%.

VLOOKUP Error Messages

The following error messages are associated with VLOOKUP.

#N/A ("value not available")

This error occurs when the search_key is not found in the first column of the range argument, or if the range argument is inaccurate. For example, the argument may include empty columns on the left side of the range.

You can also get this error if the is_sorted argument is set to FALSE and an exact match for the search_key argument cannot be found in the first column of the range. Likewise, the error will occur if the is_sorted argument is set to TRUE and all of the values in the first column of the range are larger than the search_key.