Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How and When to Do a VLOOKUP in Google Sheets

VLOOKUP Google Sheets function is design to vertically search the unique value from the first column of data range and return the matching value from the same row, one of the columns to the right from the same sheet, or different sheet. The Google Sheets VLOOKUP function is one of the most common functions used to retrieve the information based on exact and approximate match types, and it also supports partial match using wildcard characters like “*” and “?

Syntax for the Google Sheets VLOOKUP function

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

Where,

Search_key – The value to be searched in the data range. It is also called lookup value or unique identifier

Range – The data table having a data set of two or more columns where data is arranged in vertical rows, and lookup values are present in the first column of a data table.

Index – The column number in a range from which data is retrieved that is related to matching lookup value in the same row.

Is_sorted – It is match type based on which you want to return lookup value; either exact match (FALSE) or approximate match (TRUE). It is optional input.

FALSE = It searches for the exact match

TRUE = It searches for an approximate match, or closest match or next smaller value than search_key, if no exact match is found. It is default match type if you omit to mention in function.

Some Points to remember

You need to remember some basic points about Google Sheets VLOOKUP

  • Google Sheets VLOOKUP function is not case sensitive. It does not differentiate between uppercase and lowercase values.
  • Google Sheets VLOOKUP function searches the search_key in the first column of range, leftmost column, and retrieves the information to right columns of the range.
  • When is_sorted is TRUE (approximate match), then data range must be sorted in ascending order (lowest to highest) with respect to the first column of range, an otherwise VLOOKUP function may return incorrect results.
  • When is_sorted is FALSE (exact match), the data range is not required to sort in any order.
  • Google Sheets VLOOKUP function can search the value for a partial match using wildcard characters asterisk (*) and question mark (?). A question mark (?) is used to match any single character, but an asterisk (*) is used to match any sequence of characters.

Examples of Google Sheets VLOOKUP function

Now we will discuss some examples of Google Sheets VLOOKUP function to have more clarity on how to use this function. Suppose we have a sales data set of cigarette brands in various regions and we want to update price, sales amount and commission percentage based on sales amounts brackets.

Google Sheets VLOOKUP for Exact match

First of all, we need to update the price of each brand in the main data sheet from Price list range of each brand based on an exact match. As per above example, we have a price list of each unique brand in data range H1:I10.

When the price of each brand in each region is updated, sales amount is also updated as sales amount is the product of Quantity and Price of each carton. Google Sheets VLOOKUP for the exact match of unique Brands is as follows;

=VLOOKUP(B2,$H$1:$I$10,2,FALSE)

We need to make the range absolute by inserting $ sign with column and row references so that range reference does not change when we copy down the formula to other rows.

When we copy down the above Google Sheets VLOOKUP formula to other rows in Price column, it picks the price of each unique brand from Price list range. When the price of each Brand in each region is updated, the sales amount is also updated because it is a product of quantity and price as shown below;

Google Sheets VLOOKUP for approximate or closest match

Google Sheets VLOOKUP function searches the search_key based on approximate or closest match type. In this method, Google Sheets VLOOKUP formula first searches for an exact match of search_key, and if an exact match is not found then, formula looks for a value that is the closest match or next smaller value to search_key.

In this example, we need to update commission percentage (%) based on sales amount figure that we have already updated above. We have data range of commission percentage (%) with a breakup of Sales Amount. As we are going to search the value based on approximate or closest match type, so we need to sort the range (H12:I19) in ascending order by Sales Amount figures.

Now in column F, we need to update commission percentage based on sales amount as a search key in column E. We need to use following Google Sheet VLOOKUP formula in active cell F2 and then copy it down to other rows.

=VLOOKUP(E2,$H$12:$I$19,2,TRUE)

As you can see we have set the commission % based on various Sales Amount brackets, so Google Sheets VLOOKUP has picked up the commission % from the 2nd column for Sales Amount (search_key) in 1st column of range based on approximate or closest match type, where sales amount is less than or equal to search_key.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
I'm looking for a formula or format for if there is data in c4 on a sheet... It adds extra information underneath
Solved by X. E. in 20 mins
hi, my vlookup wont work in some rows but does in others. its temperemental. e.g it will work on rows 10 to 15, then not row 16 and 17, but the will for the rest.
Solved by K. Q. in 21 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc