Google Spreadsheets is one of the best programs to get to learn now; with its powerful functions and abilities, you can almost make anything you desire. VLOOKUP is one of the most misunderstood functions in Google Sheets. It allows you to search through and link two sets of data in your spreadsheet with a single search value. Here’s how to use it.
How VLOOKUP Works in Google Sheets
VLOOKUP might sound scary and confusing, but it’s pretty simple once you understand how it works. A formula that uses the VLOOKUP function has four arguments.
The first is the search key value you’re looking for, and the second is the cell range you’re searching (e.g., A1 to D10). The third argument is the column index number from your range to be searched, where the first column in your range is number 1, the next is number 2, and so on. The fourth argument is whether the search column has been sorted or not.
The final argument is only important if you’re looking for the closest match to your search key value. If you’d rather return exact matches to your search key, you set this argument to FALSE.
If you wanna get the most of VLOOKUP and not get confused use Named Ranges for example in the picture below the yellow table is for Products ( ID, Name, Cost) meanwhile the green table is for the addition of products in inventory.
In the picture below you can see the named data ranged the selected area has the name ITEM and is now a data table which is used by VLOOKUP in searches.
Now look at how different the formula is the second argument now is ‘ITEM’ and not a data range, this helps beginners to not get lost. The formula below searches the range with the same ID as the cell F3 and gives back the value of the second column (because the first column is ID). So here gives the product name if you place a number in the ID with cell F3.
As you can see below as soon as you hit enter after the number the formula automatically updates its data. And you get the desired result.
Use VLOOKUP with Multiple Sheets
The example above used a set of data from a single sheet, but you can also use VLOOKUP to search data across multiple sheets in a spreadsheet. In this example, the information from table A is now on a sheet called “Employees.” While table B is now on a sheet called “Birthdays.”
Instead of using a typical cell range like A3:D9, you can click on an empty cell, and then type:
=VLOOKUP(A4, Employees!A3:D9, 4, FALSE).
When you add the name of the sheet to the beginning of the cell range (Employees!A3:D9), the VLOOKUP formula can use the data from a separate sheet in its search.
Using Wildcards with VLOOKUP
Our examples above used exact search key values to locate matching data. If you don’t have an exact search key value, you can also use wildcards. Like a question mark or an asterisk, with VLOOKUP.
- Asterisk (*): Find any number of characters after a text. For example, you can use “Ex*” to match the text “Excel” from a list.
- Question Mark (?): Use a question mark to replace with a character. For example, you can use P?inter to lookup for the text “Painter” or “Printer”.
- Tilde (~): It can nullify the impact of the above two characters. For example, if you want to look up for a value “PD*”, instead you can use “PD~*”.
Here you have a list of names (First + Last) and you have to look up for the marks for the students just using the first name. When you use a normal VLOOKUP then it will return an error #N/A which means the value is not there in the list which is absolutely right.
But, when you combine the lookup value with an asterisk, you get the students marks without any error.
In the above formula, you have used an asterisk after the first name which helps VLOOKUP to lookup for a value which starts with the first name you have mentioned and the rest of the value can be anything.
Searching for the Closest Match with VLOOKUP
You can use the final argument of a VLOOKUP formula to search for either an exact or closest match to your search key value. In our previous examples, we searched for an exact match, so we set this value to FALSE.
If you want to find the closest match to a value, change VLOOKUP’s final argument to TRUE. As this argument specifies whether a range is sorted or not, make sure your search column is sorted from A-Z, or it won’t work correctly.