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

Woman on laptopGuido Mieth/Getty ImagesVLOOKUP, otherwise known as ‘vertical lookup,’ is a search action that can be used on Google Sheets similar to the way it works on Excel.
  • 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.
  • Visit Insider’s Tech Reference library for more stories.

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.

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 with Google Sheets 1Dave Johnson/InsiderThis is what the VLOOKUP formula looks like in Google Sheets.

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 with Google Sheets 5Dave Johnson/InsiderYou can use a wildcard symbol to search for a partial index term.

How to convert an Excel spreadsheet to a Google Sheets documentHow to convert your Google Sheets files into Excel files, and edit them in Microsoft ExcelHow to see the edit history for cells in Google Sheets and spot changes or mistakes in a spreadsheetHow to add cells in Google Sheets on your PC or Mac

Business Insider Emails & Alerts

Site highlights each day to your inbox.

Follow Business Insider Australia on Facebook, Twitter, LinkedIn, and Instagram.