How VLOOKUP Works in Google Sheets
by Ben Stockton
VLOOKUP might sound confusing, but its 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 youre looking for, and the second is the cell range youre 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 pieces that make up a VLOOKUP formula in Google Sheets.
The final argument is only important if youre looking for the closest match to your search key value. If youd rather return exact matches to your search key, you set this argument to FALSE.
Heres an example of how you might use VLOOKUP. A company spreadsheet might have two sheets: one with a list of products (each with an ID number and price), and a second with a list of orders.
You can use the ID number as your VLOOKUP search value to find the price for each product quickly.
One thing to note is VLOOKUP cant search through data to the left of the column index number. In most cases, you either have to disregard the data in columns to the left of your search key or place your search key data in the first column.
Using VLOOKUP on a Single Sheet
For this example, lets say you have two tables with data on a single sheet. The first table is a list of employees names, ID numbers, and birthdays.
A Google Sheets spreadsheet showing two tables of employee information.
In a second table, you can use VLOOKUP to search for data that uses any of the criteria from the first table (name, ID number, or birthday). In this example, well use VLOOKUP to provide the birthday for a specific employee ID number.
The appropriate VLOOKUP formula for this is =VLOOKUP(F4, A3:D9, 4, FALSE).
The VLOOKUP function in Google Sheets, used to match data from table A to table B.
To break this down, VLOOKUP uses the F4 cell value (123) as the search key and searches the range of cells from A3 to D9. It returns data from column number 4 in this range (column D, Birthday), and, as we want an exact match, the final argument is FALSE.
In this case, for ID number 123, VLOOKUP returns a birthdate of 19/12/1971 (using the DD/MM/YY format). Well expand this example further by adding a column to table B for surnames, making it link the birthday dates to actual people.
This requires only a simple change to the formula. In our example, in cell H4, =VLOOKUP(F4, A3:D9, 3, FALSE)searches for the surname that matches ID number 123.
VLOOKUP in Google Sheets, returning data from one table to another.
Instead of returning the birthdate, it returns the data from column number 3 (Surname) matched to the ID value located in column number 1 (ID).
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).
VLOOKUP in Google Sheets, returning data from one sheet to another.
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 dont have an exact search key value, you can also use wildcards, like a question mark or an asterisk, with VLOOKUP.
For this example, well use the same set of data from our examples above, but if we move the First Name column to column A, we can use a partial first name and an asterisk wildcard to search the surnames of employees.
The VLOOKUP formula to search for surnames using a partial first name is =VLOOKUP(B12, A3:D9, 2, FALSE); your search key value goes in cell B12.
In the example below, Chr* in cell B12 matches the surname Geek in the sample lookup table.
The results of a surname wildcard VLOOKUP search used in Google Sheets.
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 VLOOKUPs 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 wont work correctly.
In our table below, we have a list of items to buy (A3 to B9), along with item names and prices. Theyre sorted by price from lowest to highest. Our total budget to spend on a single item is $17 (cell D4). We used a VLOOKUP formula to find the most affordable item on the list.
The appropriate VLOOKUP formula for this example is =VLOOKUP(D4, A4:B9, 2, TRUE). Because this VLOOKUP formula is set to find the nearest match lower than the search value itself, it can only look for items cheaper than the set budget of $17.
In this example, the cheapest item under $17 is the bag, which costs $15, and thats the item the VLOOKUP formula returned as the result in D5.
A VLOOKUP in Google Sheets with sorted data to find the nearest value to the search key value.
READ NEXT
VLOOKUP might sound confusing, but its 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 youre looking for, and the second is the cell range youre 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 pieces that make up a VLOOKUP formula in Google Sheets.
The final argument is only important if youre looking for the closest match to your search key value. If youd rather return exact matches to your search key, you set this argument to FALSE.
Heres an example of how you might use VLOOKUP. A company spreadsheet might have two sheets: one with a list of products (each with an ID number and price), and a second with a list of orders.
You can use the ID number as your VLOOKUP search value to find the price for each product quickly.
One thing to note is VLOOKUP cant search through data to the left of the column index number. In most cases, you either have to disregard the data in columns to the left of your search key or place your search key data in the first column.
Using VLOOKUP on a Single Sheet
For this example, lets say you have two tables with data on a single sheet. The first table is a list of employees names, ID numbers, and birthdays.
A Google Sheets spreadsheet showing two tables of employee information.
In a second table, you can use VLOOKUP to search for data that uses any of the criteria from the first table (name, ID number, or birthday). In this example, well use VLOOKUP to provide the birthday for a specific employee ID number.
The appropriate VLOOKUP formula for this is =VLOOKUP(F4, A3:D9, 4, FALSE).
The VLOOKUP function in Google Sheets, used to match data from table A to table B.
To break this down, VLOOKUP uses the F4 cell value (123) as the search key and searches the range of cells from A3 to D9. It returns data from column number 4 in this range (column D, Birthday), and, as we want an exact match, the final argument is FALSE.
In this case, for ID number 123, VLOOKUP returns a birthdate of 19/12/1971 (using the DD/MM/YY format). Well expand this example further by adding a column to table B for surnames, making it link the birthday dates to actual people.
This requires only a simple change to the formula. In our example, in cell H4, =VLOOKUP(F4, A3:D9, 3, FALSE)searches for the surname that matches ID number 123.
VLOOKUP in Google Sheets, returning data from one table to another.
Instead of returning the birthdate, it returns the data from column number 3 (Surname) matched to the ID value located in column number 1 (ID).
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).
VLOOKUP in Google Sheets, returning data from one sheet to another.
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 dont have an exact search key value, you can also use wildcards, like a question mark or an asterisk, with VLOOKUP.
For this example, well use the same set of data from our examples above, but if we move the First Name column to column A, we can use a partial first name and an asterisk wildcard to search the surnames of employees.
The VLOOKUP formula to search for surnames using a partial first name is =VLOOKUP(B12, A3:D9, 2, FALSE); your search key value goes in cell B12.
In the example below, Chr* in cell B12 matches the surname Geek in the sample lookup table.
The results of a surname wildcard VLOOKUP search used in Google Sheets.
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 VLOOKUPs 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 wont work correctly.
In our table below, we have a list of items to buy (A3 to B9), along with item names and prices. Theyre sorted by price from lowest to highest. Our total budget to spend on a single item is $17 (cell D4). We used a VLOOKUP formula to find the most affordable item on the list.
The appropriate VLOOKUP formula for this example is =VLOOKUP(D4, A4:B9, 2, TRUE). Because this VLOOKUP formula is set to find the nearest match lower than the search value itself, it can only look for items cheaper than the set budget of $17.
In this example, the cheapest item under $17 is the bag, which costs $15, and thats the item the VLOOKUP formula returned as the result in D5.
A VLOOKUP in Google Sheets with sorted data to find the nearest value to the search key value.
Recent Comments