How to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheets

• You can use VLOOKUP with Google Sheets; it's similar to how the function works in Excel.
• The VLOOKUP formula can find values in tables on the same sheet or a different sheet, but it can only find values to the right of the search key.
• You can use wildcards like an asterisk and question mark to find partial matches as well.

VLOOKUP is a commonly used search function that lets you look up a value in one table and use it in another. It takes its name from the fact that it performs a "vertical lookup" - it searches a specified column vertically for a search key and then returns the value you're looking for from the same row.

VLOOKUP works more or less the same way in both Excel and Google Sheets; if you already know how to use VLOOKUP in Excel, making the transition to Google is not difficult. There's no wizard in Google Sheets, though, so you'll need to enter all the values manually.

How to use VLOOKUP with Google Sheets

The easiest way to understand how to use VLOOKUP is with an example. Imagine you have a table in a Google Sheet that represents data about products in inventory, including part number, name, price, and so on. If you want to create a second table that only includes the part number and price, you could use VLOOKUP to extract price from the spreadsheet knowing only the part number.

1. Enter the part number you want to find and then, in the field next to it, enter "=VLOOKUP" and press tab to start entering the arguments of the formula.
2. The first argument is the search key, which is the part number you just entered in the cell to the left. Either type the value into the formula or click the cell to the left to automatically enter it into the formula. Type a comma.
3. The second argument is the range. You'll want to specify the set of columns to include in the search. You can type it, as in "A:E" or click on the first column header and then drag the mouse to the last column in the table. Type a comma.
4. The third argument is the column that contains the value you're looking for. In this example, we want the price, which is the third column in the range. Type a 3 and add a comma.
5. Type "False" and add the closing parenthesis.
   

   
   The final formula will look like this:

   =VLOOKUP(G2,A:E,3,false)

   You should see the result appear in the cell with the formula.

   You can drag this cell down to copy it to subsequent cells in the column. For example, if you have a lot of parts, this would help you quickly make a table of prices for part numbers.

   One important note: VLOOKUP can't return a value that is to the left of the search key, so you'll need to be sure the value you are looking for in the table is to the right of the search key.

Understanding VLOOKUP syntax

Here is what the VLOOKUP formula looks like:

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

• The "search_key" is the value you want to search for. For example, if you want to know the price of a product, you might search for the product name or item number in the same row.
• The range is the columns that contain the data.
• The index is the column that contains the value you want to find. In the example of finding the price of a product, this is the price column. Rather than using the column letter, you number the columns in the range; the first column is 1, and so on.
• The final value is "true" or "false" and is used to indicate if the index column has been sorted. It's optional, but defaults to "true" if you don't include it - which will give you the wrong result if the table isn't sorted.

How to use VLOOKUP on multiple sheets

That procedure will help you build a VLOOKUP table if you're extracting information from a table on the same sheet, but if you want to build a table on a different sheet, you'll need to make one small change: You need to modify the range to an absolute value that references the name of the spreadsheet.

For example, suppose you have a Google Sheets workbook with a sheet named "Parts" and you want to extract prices but store them on a new sheet named "Audit." The range needs to be prefaced with the name of the sheet and an exclamation mark (!). The formula will look the same but instead of this:

=VLOOKUP(G2,A:E,3,false)

It needs to look like this:

=VLOOKUP(G2,Audit!A:E,3,false)

How to use VLOOKUP with a wildcard

You can also use VLOOKUP to search for partial matches using wildcards - you simply need to use the asterisk (*) or question mark (?) in the search key. The asterisk can be used to match any sequence of characters, while the question mark only replaces a single character.

How to use VLOOKUP to find the closest match

While most of the time you'll want to find an exact match, you might sometimes need to find the closest match. For example, suppose you are looking for a part that's less than a target price. Here's how to do that:

1. Sort the index column - in other words, the column with the price you are looking for. If you don't sort the table, you won't get accurate results. To do that, select the first row, then click "Data" in the menu. Choose "Sort sheet by column A -> Z."
2. Enter your formula as you normally would.
3. To complete the formula, set the "is_sorted" argument to "True."