How to find data in Google Sheets with VLOOKUP

VLOOKUP is one of the most misleading functions when using Google Sheets. It allows you to find and link 2 data sets in a spreadsheet with a single search value. Here's how to use VLOOKUP in Google Sheets.

• Google Sheets
• Google Sheets for iOS
• Google Sheets for Android

Unlike Microsoft Excel , Google Sheets does not provide instructions for using VLOOKUP, so you must manually type this formula.

How VLOOKUP works in Google Sheets

VLOOKUP may sound confusing but it's fairly easy to use once you know how it works. A formula using the VLOOKUP function has 4 arguments.

The first is the value of the search keyword, the second is the range of the search box (e.g. A1 to D10). The third argument is the column index number from your range to the search value - where the first column in the range is number 1, followed by number 2 and so on.

The fourth argument is whether the search column has been sorted or not.

The last argument is important only when you search for results that best match the search keyword value. If you want to return exact results for the search term, set this argument to FALSE .

Here is an example of how you can use VLOOKUP . A company spreadsheet can consist of two tables: a product list (with an ID number and a corresponding price) and a list of orders.

You can use the ID number as the VLOOKUP search value to quickly see the price for each product.

One thing to note is that VLOOKUP cannot search through the data to the left of the column index number. In most cases, you may have to ignore the data in the columns to the left of the search keyword or put the search data in the first column.

Use VLOOKUP on a spreadsheet

For example, you have two data tables on a spreadsheet. The first table is the employee name, ID number and birthday.

In the second table, you can use VLOOKUP to search data using any criteria from the first table (name, ID number or date of birth). In this example, the article uses VLOOKUP to provide a birthday for certain employee IDs.

The appropriate VLOOKUP function formula is = VLOOKUP (F4, A3: D9, 4, FALSE) .

Specifically, VLOOKUP uses the value of cell F4 (123) as the focus of the search and the range of finding data from cells A3 to D9. It returns the result from column 4 in this range (column D - Birthday) and because to get the exact result, the final argument is FALSE.

In this case, with ID number 123, VLOOKUP returns the date of birth: December 19, 1971 (using the format DD / MM / YY). The article extends this data by adding a column to table B for a surname. This causes it to associate the birthday with the corresponding person.

This task only requires a simple formula change. In this example, in cell H4, = VLOOKUP (F4, A3: D9, 3, FALSE) searches for a surname that matches the ID number 123.

Instead of the date of birth, it returns the data from column number 3 (Surname) matching the ID value located in column number 1 (ID).

Use VLOOKUP with multiple spreadsheets

The example above uses a set of data from a single sheet, but you can also use VLOOKUP to find information on multiple worksheets. In this example, information from table A is now on sheet "Employees", while table B is on sheet "Birthdays".

Instead of using a typical range of cells like A3: D9, you can click on an empty cell, and type: = VLOOKUP (A4, Employees! A3: D9, 4, FALSE) .

When naming a sheet to start a range of cells (Employees! A3: D9), the VLOOKUP function can use data from a separate sheet in its search field.

Use wildcards with VLOOKUP

The above example used the exact search value to determine the matching data. If you don't have this value, you can also use wildcards, such as question marks or asterisks with VLOOKUP.

The article will use the same data set above as an example, but, "First Name" will move to column A. At that time, you can use part of the main name and wildcard to find the last name of the employee. .

The VLOOKUP function finds a last name by the name = VLOOKUP (B12, A3: D9, 2, FALSE) ; The search keyword value is in cell B12.

In the example below, the "Chr *" in cell B12 matches the "Geek" last name in this sample lookup table.

Search for the best match with VLOOKUP

You can use the last argument of the VLOOKUP function to search for an exact match or closest to the search keyword value. In the previous example, we found the exact result, so the final value is FALSE.

If you want to find the result closest to the value, change the last argument of VLOOKUP to TRUE. Because this argument determines whether a range is categorized, make sure the search column is filtered in AZ order. Otherwise, this function will not work correctly.

The table below is a list of items to buy (A3 to B9) with names and corresponding prices. They have been sorted by price from lowest to highest. The total budget for a good is 17USD (cell D4). The article used VLOOKUP formula to find the most suitable product on this list.

The appropriate VLOOK formula for this example is = VLOOKUP (D4, A4: B9, 2, TRUE) . Because the VLOOKUP function is set to find the closest value, lower than the search value, it can only find items that are cheaper than $ 17.

In this example, the cheapest product under $ 17 is a bag ($ 15). As you can see, the result returns in cell D5.

Here's how to use VLOOKUP to search data on Google Sheets. Hope the article is helpful to you.