How To Software Find Data in Google Spreadsheets with VLOOKUP Share Pin Email Print Software Spreadsheets Documents Presentations Desktop Publishing Graphic Design Databases Animation & Video by Ted French Updated May 16, 2017 01 of 03 Find Price Discounts with VLOOKUP Google Spreadsheets VLOOKUP Function. © Ted French How the VLOOKUP Function WorksGoogle 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 3VLOOKUP 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 dataYou supply the column number - known as the index - of the data you seekThe function looks for the search_key in the first column of the data tableVLOOKUP then locates and returns the information you seek from another field of the same record using the supplied index numberFinding Approximate Matches with VLOOKUPNormally, 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 FirstAlthough 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 ExampleThe 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 FunctionThe 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 displayedType the equal sign ( = ) followed by the name of the function vlookupAs you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter VWhen 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 B2Entering the Function ArgumentsDetailed information on VLOOKUP's syntax and arguments can be found on page 2As 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 argumentAfter the cell reference, type a comma ( , ) to act as a separator between the argumentsHighlight cells A5 to B8 in the worksheet to enter these cell references as the range argument - the table headings are not included in the rangeAfter the cell reference, type another commaType a 2 after the comma to enter the index argument since the discount rates are located in column 2 of the range argumentAfter the number 2, type another commaHighlight cells B3 and B4 in the worksheet to enter these cell references as the holiday argumentType the word True after the comma as the is_sorted argumentPress the Enter key on the keyboard to enter a closing round bracket " ) " after the function's last argument and to complete the functionThe answer 2.5% - the discount rate for the quantity purchased - should appear in cell B2 of the worksheetWhen you click on cell B2, the complete function =VLOOKUP(A2, A4:B8, 2, True) appears in the formula bar above the worksheetWhy VLOOKUP Returned 2.5% as a ResultIn 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%. 02 of 03 Google Spreadsheets VLOOKUP Function's Syntax and Arguments Google Spreadsheets VLOOKUP Function. © Ted French The VLOOKUP Function's Syntax and ArgumentsA 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 aboverange - (required) the number of columns and rows that VLOOKUP should search- the first column in the range normally contains the search_keyindex - (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 functionis_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 03 of 03 VLOOKUP Error Messages Google Spreadsheets VLOOKUP Function Error Messages. © Ted French VLOOKUP Error MessagesThe 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 argumentThe range argument is inaccurate. For example, the argument may include empty columns on the left side of the rangeThe 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 rangeThe is_sorted argument is set to TRUE and all of the values in the first column of the range are larger than the search_keyA #REF! ("reference out of range") error is displayed if:The index argument is greater than the number of columns in the range argument Was this page helpful? Thanks for letting us know! Share Pin Email Tell us why! Other Not enough details Hard to understand Submit Continue Reading