03/12/2021

IOS KingMan

IOS KingMan

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.
  • 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. 
This is what the VLOOKUP formula looks like in Google Sheets.
Dave Johnson/Insider

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.

You can use a wildcard symbol to search for a partial index term.
Dave Johnson/Insider