Find Data in Google Spreadsheets with VLOOKUP

How the VLOOKUP Function Works

Google Spreadsheets' VLOOKUP function, which stands for vertical lookup, can be used to look up specific information located in a table of data or database.

• Information on VLOOKUP error messages can be found on page 3

VLOOKUP normally returns a single field of data as its output. How it does this is:

1. You provide a name or search_key that tells VLOOKUP in which row or record of the data table to look for the desired data
2. You supply the column number - known as the index - of the data you seek
3. The function looks for the search_key in the first column of the data table
4. VLOOKUP then locates and returns the information you seek from another field of the same record using the supplied index number

Finding Approximate Matches with VLOOKUP

Normally, VLOOKUP tries to find an exact match for the search_key indicated. If an exact match cannot be found, VLOOKUP can find an approximate match.

• An approximate match is the nearest match to the search_key in size that is smaller or less in value.
• Approximate matches are found by setting the function's is_sorted argument to TRUE.

Sorting the Data First

Although not always required, it is a 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.

VLOOKUP Function Example

The example in the image above uses the following formula containing the VLOOKUP function to find the discount for quantities of goods purchased.

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

Even though the above formula can just be typed into a worksheet cell, another option, as used with the steps listed below, is to use Google Spreadsheets auto-suggest box to enter the formula.

Entering the VLOOKUP Function

The steps for entering the VLOOKUP function shown in the image above into cell B2 are:

1. Click on cell B2 to make it the active cell - this is where the results of the VLOOKUP function will be displayed
2. Type the equal sign ( = ) followed by the name of the function vlookup
3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter V
4. When the name VLOOKUP appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell B2

Entering the Function Arguments

• Detailed information on VLOOKUP's syntax and arguments can be found on page 2
• As in Excel, a comma is placed between the function's arguments to act as a separator.

The arguments for the VLOOKUP function are entered after the open round bracket in cell B2.

1. Click on cell A2 in the worksheet to enter this cell reference as the search_key argument
2. After the cell reference, type a comma ( , ) to act as a separator between the arguments
3. Highlight cells A5 to B8 in the worksheet to enter these cell references as the range argument - the table headings are not included in the range
4. After the cell reference, type another comma
5. Type a 2 after the comma to enter the index argument since the discount rates are located in column 2 of the range argument
6. After the number 2, type another comma
7. Highlight cells B3 and B4 in the worksheet to enter these cell references as the holiday argument
8. Type the word True after the comma as the is_sorted argument
9. Press the Enter key on the keyboard to enter a closing round bracket " ) " after the function's last argument and to complete the function
10. The answer 2.5% - the discount rate for the quantity purchased - should appear in cell B2 of the worksheet
11. When you click on cell B2, the complete function =VLOOKUP(A2, A4:B8, 2, True) appears in the formula bar above the worksheet

Why VLOOKUP Returned 2.5% as a Result

• 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 will find an approximate match to the search_key value.
• The nearest value in size that is still smaller than the search_key value of 23 is 21.
• VLOOKUP, therefore, looks for the discount percent in the row containing 21, and, as a result, returns a discount rate of 2.5%.

The VLOOKUP Function's Syntax and Arguments

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)

search_key - (required) the value to search for - such as the quantity sold in the image above

range - (required) the number of columns and rows that VLOOKUP should search
- the first column in the range normally contains the search_key

index - (required) the column number of the value you want found
- the numbering begins with the search_key column as column 1
- if index is set to a number greater than the number of columns selected in the range argument a #REF! error is returned by the function

is_sorted - (optional) indicates whether or not the range is sorted in ascending order  using the first column of the range for the sort key
- a Boolean value - TRUE or FALSE are the only acceptable values
- if set to TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur
- if omitted, the value is set to TRUE by default
- if set to TRUE or omitted and an exact match for the search_key is not found, the nearest match that is smaller in size or value is used as the search_key.
- if set to FALSE, VLOOKUP only accepts an exact match for the search_key. If there are multiple matching values, the first matching value is returned
- if set to FALSE, and no matching value for the search_key is found a #N/A error is returned by the function

VLOOKUP Error Messages

The following error messages are associated with VLOOKUP.

An #N/A ("value not available") error is displayed if:

• The search_key is not found in the first column of the range argument
• The range argument is inaccurate. For example, the argument may include empty columns on the left side of the range
• 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
• 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

A #REF! ("reference out of range") error is displayed if:

• The index argument is greater than the number of columns in the range argument