VLOOKUP in Google Sheets

Reading Time: 4 minutes

The VLOOKUP function searches for a key value down the first column in a specified range and returns a value in the same row from another column.

Syntax
=VLOOKUP(search_key, range, index, [is_sorted])

search_key: The value to search for, can be a number or a string

range: The range for the search, the first column in the range is specified as the search key

index: The column number of the column to be returned, the first column is numbered 1.

is_sorted: It is an optional argument that is set TRUE by default. FALSE returns the exact match; TRUE the approximate match.

Sample Usage
=VLOOKUP("Monica", A2:B7, 2, FALSE)

//Lookup for “Monica” from the range A2:B7 and returns the corresponding value from the second column.

Use VLOOKUP in Google Sheets (Step-by-Step)

We will learn about Vlookup in Google Sheets with several examples. We will also learn some advanced Vlookup features.

To perform vertical lookups, see Hlookup.

Need for Vlookup in Google Sheets

Vlookup means vertical lookup. It is a function that allows searching in a column and can return any selected value from the same row. It is one of the more advanced features in Google Sheets and is very useful when you have a large set of data and want to quickly access data for a specific key. 

Vlookup in Google Sheets: Syntax

The syntax for VLOOKUP in Google Sheets is 

=VLOOKUP(search_key, range, index, [is_sorted])

• Search_key: The value to search for, can be a number or a string
• Range: The range for the search, the first column in the range is specified as the search key
• Index: The column number of the column to be returned, the first column is numbered 1.
• Is_sorted: It is an optional argument that is set TRUE by default. It indicates whether the first column of the specified range (the column to be searched is sorted or not). It is recommended to keep this false to avoid any errors.
  • If set to FALSE- If set to FALSE, an exact match is returned. If there are multiple matching values, the first matched cell is returned and #N/A is returned if no value is found
  • If set to TRUE or omitted- If the search array is not sorted then it might return a wrong value, but if it is correctly used then the nearest match (less than or equal to the search key) is returned. Set it to true only if you have large data and want to optimize your searches.

Vlookup: Basic Usage

Suppose we have a Table of sales by different people and we want to fetch the sales made by an individual, let’s say, Monica. To achieve the same, we can write the following function.

=VLOOKUP(“Monica”, A2:B7, 2, FALSE)

Here “Monica” is the search_key. A2:B7 is the data range in consideration. 2 is the column number we want to return(i.e. Sales column). The is_sorted variable is False is because the first row isn’t sorted.

Here our range is A2:B7, covering the entire table. The function would search in the first column, i.e. the “Name” section, and return the 2nd column data(i.e.Sales) in the resultant row. Hence our function returns the value 65.

We can also add references to the search_key so that you can change them dynamically.

Return the entire row in Vlookup in Google Sheets

If you want to return an entire row for the search_key instead of just one field, you can use the ArrayFormula function. An example for the same would look something like this.

We used ARRAYFORMULA and iterated over {2,3}, i.e. the indices we wanted to return.

Using Wildcard Characters in Vlookup

If you want to use wildcard characters, you can do so too whilst using Vlookup. For example in the following, just searching for Mo* would return us results for “Monica”.  Also, one thing to notice here is, if it matches with more than one entry. It would return the first entry in the search range.

We used the ‘*’ special character here, which means, it can be replaced by any number of characters. You can also use ‘?’(replace by 1 character) or ‘~’(escape character for ‘*’ and ‘?’).

Vlookup from other sheets

In current scenarios, often can happen that the lookup table resides in a different tab, to refer lookup from another sheet, you can use the following syntax.

=VLOOKUP(search_key, sheet_name!range, index, [is_sorted])

As you can see, we are referencing the data from another sheet, namely “sheet1”. To perform lookups in the vertical table, see Hlookup.

Conclusion

We learned about Google Sheets Vlookup and its example uses. We also saw some scenarios in which you can modify this versatile function. This article talks about the basic difference between Vlookup and Indexmatching which you can check out.

See Also

You can check out other equal good articles on Google Sheets here.

Google Sheets: Hlookup | Quick Guide: Introduction to Hlookup in Google Sheets

Using Vlookup and Hlookup together: Learn how to perform 2-D lookups by combining Vlookup and Hlookup