vlookup definition

How to Use VLookup in Google Sheets

by

Of all the functions in Google Sheets, the VLOOKUP, or Vertical Lookup, is one of the most confusing to new spreadsheet users and is somewhat of a rite of passage to learn. The VLOOKUP helps stitch existing and new data automatically without having you do it manually. In this article, we’ll break the VLOOKUP into its parts or arguments and variations for single or multiple spreadsheets.

The VLOOKUP Broken Up into Parts

Typing in “=vlookup(” for the first time is confusing to most of us since we’re greeted with an example that looks like this:

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

Before breaking up the VLOOKUP into its parts, we can think of it as finding a Key-Value pair. The function will first find a Key in another data set then bring back the Value matches what we want.

Now that we understand key-value pairs, let’s break up the formula into its 4 parts:

  1. Search Key

    The search key will be the “key” of the key-value pair you are looking for. Note: The Search Key column of the VLOOKUP must be to the left of the Index column.

  2. Range

    The range of dataset you will be searching for your key-value pair. The range could be on the same sheet or on a different sheet on the same file.

  3. Index

    The index will be the column where you find the “value” part of the key-value pair. The index number will correspond to the column where you want to pull the value.

    Example: Column A = 1, Column B = 2, Column C = 3, and so on.

  4. Is Sorted

    The is_sorted variable is TRUE (or 1) by default or can be specified as FALSE (or 0). The VLOOKUP will behave differently depending on what value you leave here:

    FALSE (0): Entering FALSE or 0 will have the VLOOKUP find exact values in the Index that you specify. Most often we use FALSE since we are looking to add combine exact data sets.

    TRUE (1 or blank): Leaving the last variable blank (or adding 1 or TRUE) will have the VLOOKUP find and return the Index column to the closest matching Search Key (if an exact match isn’t found). The Search Key column must be sorted or errors can occur.

Vlookup with Data from a Different Sheet

Most of the time you will be using VLOOKUPs for taking data from an existing sheet and adding it to your current sheet. Although it might seem more complex, it actually isn’t.

Vlookups using another sheet only modifies the Range argument of the function. While a VLOOKUP  within the same sheet might look like this:
=vlookup(A1,D1:F10,6,0)

A VLOOKUP on another might look like:

=vlookup(A1,Sheet2!D1:F10,6,0)

The sole difference is adding the “Sheet2!” for looking up values on another sheet. The Range argument adds “[Other Sheet Name]!” to the range of the sheet you are using the VLOOKUP.