vlookup is a robust tool that permits users to look for specific data in a big dataset. Whether you are a business owner or just someone who works with data, mastering the vlookup function can prevent time and show you how to make more informed decisions.
You is perhaps an entire beginner to vlookup. Or perhaps you’re more accustomed to Excel and wish to know find out how to execute this formula in Google Sheets.
Either way, you’ll find step-by-step instructions and useful suggestions below to be sure you’re using the vlookup function appropriately and retrieving accurate results out of your dataset.
Table of Contents
What does vlookup do in Google Sheets?
Vlookup is a function in Google Sheets that searches for a selected value within the leftmost column of a table or range and returns a corresponding value from a specified column inside that range.
The syntax for the vlookup function is as follows:
Vlookup(search_key, range, index, [is_sorted])
- search_key is the worth that you would like to seek for.
- range is the table or range that you would like to search in.
- index is the column number (ranging from 1) of the worth you would like to retrieve.
- is_sorted is an optional argument that indicates whether the information within the range is sorted in ascending order. If this argument is ready to TRUE or omitted, the function assumes that the information is sorted and uses a faster search algorithm. If this argument is ready to FALSE, the function uses a slower search algorithm that works for unsorted data.
For instance, if you’ve a table with an inventory of product names in the primary column and their corresponding prices within the second column, you should use the vlookup function to look up the value of a selected product based on its name.
The Advantages of Using vlookup in Google Sheets
Using vlookup can prevent a number of time when looking through large datasets. It’s a terrific option to quickly find the information you would like without having to scroll through tons of of rows manually.
Using vlookup in Google Sheets also:
- Saves effort and time. You may quickly retrieve information from large datasets by automating the search and retrieval process through vlookup. This will prevent a number of effort and time in comparison with manually looking for information in a table.
- Reduces errors. When looking for information manually, there’s a risk of human error, corresponding to mistyping or misreading information. Vlookup can show you how to avoid these errors by performing accurate searches based on exact matches.
- Increases accuracy. Vlookup helps be sure that you’re retrieving the proper information by allowing you to look for specific values in a table. This will show you how to avoid retrieving incorrect or irrelevant information.
- Improves data evaluation. You may analyze data more efficiently through the use of vlookup to check and retrieve data from different tables. This will show you how to easily discover patterns, trends, and relationships between data points.
- Provides flexibility and customization. Vlookup lets you specify the search criteria and select which columns to retrieve data from, making it a flexible and customizable tool that could be used for a wide selection of tasks.
Easy methods to Use vlookup in Google Sheets
- Open a latest or existing Google Sheet.
- Enter the information you would like to seek for in a single column of the sheet. For instance, you would possibly have an inventory of product names in column A.
- Enter the corresponding data you would like to retrieve in one other column of the sheet. For instance, you would possibly have an inventory of costs in column B.
- Determine which cell you would like to use to enter the vlookup formula, and click on on that cell to pick it.
- Type the next formula into the cell:
=VLOOKUP(search_key, range, index, [is_sorted])
- Replace the “search_key” argument with a reference to the cell containing the worth you would like to seek for. For instance, if you would like to seek for the value of a product named “Milk” and “Milk” is in cell A1, you’d replace “search_key” with “A1”
- Replace the “range” argument with a reference to the range of cells that incorporates the information you would like to search in.
For instance, in case your product names are in column A and your prices are in column B, you’d replace “range” with “A:B”.
It’s also possible to just click and drag your mouse over the range of cells the vlookup should use to retrieve the information in case you’re working with a smaller dataset.
- Replace the “index” argument with the variety of the column containing the information you would like to retrieve. For instance, if you would like to retrieve prices from column B, you’d replace “index” with “2”.
- If the information in your range is sorted in ascending order, you possibly can omit the ultimate “[is_sorted]” argument or set it to “TRUE”. If the information isn’t sorted, it is best to set this argument to “FALSE” to make sure accurate results.
- Press Enter to use the formula and retrieve the specified data.
That is it! The vlookup function should now retrieve the corresponding data based on the search key you specified. You may copy the formula to other cells within the sheet to retrieve additional data.
vlookup Example
Let’s take a take a look at a practical example of find out how to use the vlookup function in Google Sheets.
Suppose you’ve a table that lists the names of employees in column A and their corresponding salaries in column B. You need to look up the salary of an worker named “John” using the vlookup function.
Once the information is entered right into a Google Sheet, you must determine which cell you would like to use to enter the vlookup formula, and click on on that cell to pick it before typing in the next formula:
=VLOOKUP(“John”, A:B, 2, FALSE)
The vlookup function should now retrieve the salary of John, which is 50,000. Here’s how the formula works:
In the primary argument, “John” is the search key, which is the worth you would like to look up within the leftmost column of the table. Within the second argument, “A:B” is the range you would like to search in, which incorporates each columns A and B.
Within the third argument, “2” is the index of the column you would like to retrieve data from, which is column B (since salaries are listed in column B).
The fourth argument, “FALSE”, indicates that the information within the range isn’t sorted in ascending order.
So the formula searches for the name “John” within the leftmost column of the table, finds the corresponding salary in column B, and returns that value (50,000).
Best Practices for Using vlookup
There are a couple of key things to recollect when using vlookup in Google Sheets to make sure it really works properly and returns accurate data.
Ensure that the information is in the identical row.
First, be sure that the information you would like to return is in the identical row as the worth you are looking for. Otherwise, vlookup won’t have the ability to seek out it.
Sort the primary column by ascending order.
Ensure that that the primary column of your data range is sorted in ascending order.
It will be sure that the vlookup function returns the proper results. If not, be sure you utilize the FALSE argument within the formula.
Include headers within the vlookup formula.
In case your data range includes headers, make sure you include them in your vlookup formula in order that the function knows where to seek out the relevant data. Otherwise, the function may not know which column to look in and will return incorrect results.
For instance, in case your columns have headers in Row 1 of the sheet corresponding to “Price,” “Name,” or “Category,” be sure these cells are included within the “range” section of the formula.
Make use of the wildcard character.
The wildcard character (*) could be utilized in the lookup value to represent any combination of characters.
For instance, suppose you’ve an inventory of product names in the primary column of an information range, and you would like to look up the sales for a product called “Chocolate Bar.”
Nevertheless, the name of the product in the information range is listed as “Chocolate Bar – Milk Chocolate.” On this case, an actual match lookup wouldn’t find the sales for the “Chocolate Bar” product.
Here is the way you would come with the wildcard character within the Google Sheets vlookup formula:
=VLOOKUP(“Chocolate Bar*”, A2:B10, 2, FALSE)
It is vital to notice that when using a wildcard character, vlookup will return the primary match it finds in the primary column of the information range that matches the lookup value.
If there are multiple matches, it’s going to return the primary one it finds. Subsequently, it is vital to be sure that the lookup value is restricted enough to return the specified result.
Match your formula to the case of the information you’re searching for.
Keep in mind that vlookup is case-sensitive, so the worth you enter into the formula must match the case of the worth within the cells.
For instance, as an instance you’ve an information range that features a column of product names, and the product names are listed in several cases in several cells, corresponding to “apple,” “Apple,” and “APPLE.”
If you happen to’re using VLOOKUP to look for the sales of a specific product, you must be sure that the lookup value in your formula matches the case of the information in the information range.
Getting Began
The vlookup function in Google Sheets is incredibly useful in case you’re coping with large datasets in complex spreadsheets. It will probably seem complicated to make use of at first, but with a little bit of practice, you’ll get the hang of it.
Just remember to maintain best practices in mind and, in case your vlookup isn’t working, use the information above to troubleshoot.
