Vertical lookup. Returns the values in a data column at the position where a match was found in the search column. VLOOKUP("Apple",table_name!fruit,table_name!price) VLOOKUP(search_key, range,index, is_sorted) search_key : The value to search for in the search column. search_column : The data column to consider for the search. result_column : The data column to consider for the result. is_sorted : [OPTIONAL] The manner in which to find a match for the search_key . FALSE : For an exact match, this is recommended. TRUE : For an approximate match, this is the default if is_sorted is unspecified. Tip: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value. Learn why you may encounter a wrong return value . FALSE : For an exact match, this is recommended. TRUE : For an approximate match, this is the default if is_sorted is unspecified. Tip: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value. Learn why you may encounter a wrong return value . Tip: For more flexible database queries in BigQuery, use XLOOKUP . =VLOOKUP( search_key, range, index, [ is_sorted ]) search_key : The value to search for in the first column of the range. range : The upper and lower values to consider for the search. index : The index of the column with the return value of the range. The index must be a positive integer. is_sorted : Optional input. Choose an option: FALSE = Exact match. This is recommended. TRUE = Approximate match. This is the default if is_sorted is unspecified. Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value. Learn why you may encounter a wrong return value . FALSE = Exact match. This is recommended. TRUE = Approximate match. This is the default if is_sorted is unspecified. Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value. Learn why you may encounter a wrong return value . The function searches for the specified search key in its first column. VLOOKUP returns the value from the column specified by index . You can also use a named range. The smallest possible index is 1. The largest possible index is the maximum number of columns in that range . If is_sorted is TRUE , then VLOOKUP uses approximate match. Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may get an unexpected value returned. Learn why you may encounter a wrong return value . If is_sorted is FALSE , then VLOOKUP uses exact match. If is_sorted is FALSE , then VLOOKUP uses exact match. If is_sorted is not specified, it is TRUE by default. Use FALSE for is_sorted due to its consistent behavior whether or not the search key column is sorted. Always specify is_sorted for better readability, even though the input is optional. If there are multiple search key values that match, the value in the return value column whose associated search key is first matched in the search key column is returned. If #N/A is returned, a value isn’t found. Basic VLOOKUP examples: VLOOKUP on different search keys Use VLOOKUP to find the price of an Orange and Apple. When you use VLOOKUP , you can use different search keys such as "Apple" and "Orange." VLOOKUP on different column indexes

VLOOKUP exact match or approximate match Use VLOOKUP exact match to find an exact ID. Use VLOOKUP approximate match to find the approximate ID. Common VLOOKUP applications Replace error value from VLOOKUP Tip: If you want to replace other errors such as #REF!, learn more about IFERROR() . VLOOKUP with multiple criteria VLOOKUP with wildcard or partial matches A question mark "?" matches any single character. An asterisk "*" matches any sequence of characters. Troubleshoot errors & best practices: Returns an unexpected value: If you set is_sorted as TRUE , but your first column in the range isn’t sorted numerically or alphabetically in ascending order, then change is_sorted to FALSE . Returns an unexpected value: If you set is_sorted as TRUE , but your first column in the range isn’t sorted numerically or alphabetically in ascending order, then change is_sorted to FALSE . VLOOKUP gives the first match: VLOOKUP only returns the first match. If you have multiple matched search keys, a value is returned, but it may not be the expected value. Unclean data: Sometimes, values with spaces that trail and lead may seem similar but VLOOKUP treats them differently. For example, the following are different to VLOOKUP : " Apple" "Apple " "Apple" If approximate or is_sorted = TRUE is used and if the search key in VLOOKUP is smaller than the smallest value in the first column, then VLOOKUP returns #N/A. If exact match or is_sorted = FALSE is used, then the exact match of the search key in VLOOKUP isn’t found in the first column. If you don’t want #N/A when the search key isn’t found in the first column, you can use the function IFNA() . Count the columns from the selected range , not the entire table. Start to count from 1 instead of 0. Incorrectly input the text or the column name for the index . Entered a number smaller than 1 for the index . The index must be at least equal to 1 and smaller than the maximum number of columns of the range . VLOOKUP can only search in the search key column, when index = 1, or columns that are further right. Important: index only accepts a number. You might have missed a quote in the search key when your search_key is text data. Absolute reference for VLOOKUP range VLOOKUP(G3, $B$3:$D$7, 3, FALSE) VLOOKUP(G3, B3:D7, 3, FALSE) Spaces that lead: " apple" Spaces that trail: "apple " Blanks or spaces: "" and " " aren’t equivalent On the top of Sheets, select your search key column. Tap Format Menu Number . Choose an option depending on your desired data type: Date Number Need more help? Try these next steps: Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.