### Find Price Discounts 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:

- 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 - You supply the column number - known as the
*index*- of the data you seek - The function looks for the
*search_key*in the first column of the data table - 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:

- Click on cell B2 to make it the active cell - this is where the results of the VLOOKUP function will be displayed
- Type the equal sign ( = ) followed by the name of the function
*vlookup* - As you type, the
*auto-suggest*box appears with the names and syntax of functions that begin with the letter V - 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 below
- 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.

- Click on cell A2 in the worksheet to enter this cell reference as the
*search_key*argument - After the cell reference, type a comma (
**,**) to act as a separator between the arguments - 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 - After the cell reference, type another comma
- Type a
**2**after the comma to enter the*index*argument since the discount rates are located in column 2 of the range argument - After the number 2, type another comma
- Highlight cells B3 and B4 in the worksheet to enter these cell references as the
*holiday*argument - Type the word
*True*after the comma as the*is_sorted*argument - Press the
*Enter*key on the keyboard to enter a closing round bracket "**)**" after the function's last argument and to complete the function - The answer 2.5% - the discount rate for the quantity purchased - should appear in cell B2 of the worksheet
- 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%.

### Google Spreadsheets VLOOKUP Function's Syntax and Arguments

### 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

### 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