Back to Blog

VLOOKUP Explained: How to Search Data Vertically in Spreadsheets

How do you learn the status of your flight on the flight board in the airport? First, complete a vertical lookup to find the necessary flight number, for example, AC5704. After doing so, shift your eyes to the right to find the respective value in the Status column.

The VLOOKUP function in Google Sheets works in a similar way as it searches vertically the specified value and returns matching data from the row. Read this tutorial to explore basic and advanced capabilities of VLOOKUP.

Understanding vertical lookup or VLOOKUP for dummies

VLOOKUP formula syntax

=VLOOKUP(search-key, range, column-index, [sorted/not-sorted])
  • search-key – specify the search key you’re going to look up. The search-key can be specified as a text string ("Text"), number (5), date (2020-01-02), or a cell reference (A2). Text to search must be entered in quotes only.
  • range – specify the data range on any sheet to look up. 
  • column-index – specify the number of the lookup column of the range from which the matching value will be returned. The index of the first column of the range is always 1.
  • [sorted/not-sorted] – specify whether the lookup column is sorted from A to Z (TRUE) or not (FALSE). It is an optional parameter with TRUE as the default value.

VLOOKUP always returns the first value found, even if the looked up column contains two or more values matching the search-key.

Vlookup formula example

Of the following data set, let’s extract the price of the Club Sandwich.

Here is the VLOOKUP formula to do this:

={"Club Sandwich",
   vlookup(
      A9,
      A2:E11,
      5,
      false
   )
}
  • {"Club Sandwich"...} – this will add the name (Club Sandwich) to the cell to the left of the value returned by the VLOOKUP formula
  • A9 – the search-key we’re looking up
  • A2:E11 – the data range 
  • 5 – the column number of the data range to look up
  • false – the lookup column is not sorted

That’s the basics. Now let’s dive deeper into how you can use VLOOKUP.

VLOOKUP Cheat Sheet 

If you are not a novice to VLOOKUP and merely need to brush up your skills, check out the following VLOOKUP cheat sheet.

VLOOKUP Cheat Sheet in Google Sheets

VLOOKUP tutorial with examples

Before anatomizing the VLOOKUP function Google Sheets, we need to get another data set to use for examples. For this, we imported the San Francisco weather forecast data from visualcrossing.com directly into Google Sheets using Coupler.io CSV importer

Coupler.io is a solution to automatically import data from different sources including Xero, BigQuery, WordPress, and many more. You can install it directly from the Google Workspace Marketplace and set up the importer you need.

By the way, the data set we used for the basic VLOOKUP formula example was also imported into Google Sheets from Airtable with Coupler.io. Now, let’s go over the VLOOKUP quirks.

VLOOKUP for the exact or closest match

  • For an exact match, specify FALSE as the [sorted/not-sorted] parameter. It’s the recommended value.
  • For the closest match, specify TRUE as the [sorted/not-sorted] parameter.

The logic of VLOOKUP with TRUE (sorted parameter) to find the closest match

If you specify TRUE as the last parameter in your VLOOKUP formula, it will search for the exact match first. If failed, the formula will search cell by cell for the closest match that is less than or equal to the search-key. If the search-key is less than the value in the first cell to look up, the formula will return an #N/A Error: Did not find value '***' in VLOOKUP evaluation.

Sheet with formulas

How to Vlookup for a partial match using wildcards

You can use the following wildcards with your search-key in the VLOOKUP formula:

  • Asterisk (*) to take the place of any number of characters.
  • Question mark (?) to take the place of any single character.
  • Tilde sign (~) before an asterisk (*) or a question mark (?) to treat them as simple signs. For example, “~**” means that you’re looking for the values that start with an asterisk “*”.

In our blog post about COUNTIF and COUNTIFS functions, we’ve already explained how wildcards work. Let’s look at it through a hands-on example, when a search-key begins, ends, or contains a specific text.
In our data set of sandwiches, we need to find the description of the product whose name contains “cake“. Here is how the search-key should look: "*cake*". And here is the VLOOKUP formula:

=vlookup(
   "*cake*",
   Products!A2:E,
   4,
   false
)

As we expected, the formula returned the description of Crab cake sandwich. In a similar manner, you can vertically look up criteria that begin or end with a particular text.

Sheet with the formula

How to Vlookup multiple columns

Let’s say the cell A1 contains “jucy“, and the cell B1 contains “lucy“. We need to use values from these cells as a search criteria ("jucy lucy") in the VLOOKUP formula. For this, you can merge cells using the ampersand (&) as follows: A1&" "&B1.  Read more about Merging Cells in Google Sheets.

Now you can nest this formula with VLOOKUP:

=vlookup(
   A1&" "&B1,
   Products!A2:E,
   4,
   false
)

Sheet with the formula

How to do a case-sensitive Vlookup: QUERY and VLOOKUP 

VLOOKUP does not differentiate between uppercase and lowercase values. So, with the search-key "cheeseburger", the formula will return the value that matches “Cheeseburger“, since it’s the first match when looking up:

={"cheeseburger",
   vlookup(
      "cheeseburger",
      A2:E12,
      5,
      false
   )
}

Nesting VLOOKUP with QUERY can solve this issue. The QUERY function is case-sensitive. The following formula will filter out “cheeseburger” from the data range A2:E12:

=query(A2:E12,"select * where A='cheeseburger'")

Nest this QUERY formula with the VLOOKUP formula to do a case-sensitive lookup:

={
   "cheeseburger",
    vlookup(
       "cheeseburger",
        query(A2:E12, "select * where A='cheeseburger'"),
        5,
        false
     )
}

Sheet with the formula

Read our blog post to learn more about the power of the Google Sheets Query Function.

How to Vlookup multiple criteria: ARRAYFORMULA and VLOOKUP

To Vlookup two or more search-keys, nest VLOOKUP with ARRAYFORMULA as follows:

=ARRAYFORMULA(VLOOKUP({search-key#1;search-key#2;...}, range, column-index, [sorted/not-sorted])

This will return vlookup results in a column. If you want to return the results in a row, replace semicolons with commas between search keys.

=ARRAYFORMULA(VLOOKUP({search-key#1,search-key#2,...}, range, column-index, [sorted/not-sorted])

For example, let’s vertically look up the price of "Veggie burger", "Club sandwich", and "Italian beef":

={
   "Veggie burger", "Club sandwich", "Italian beef";
   arrayformula(
      vlookup(
         {Products!A2,Products!A9,Products!A6},
         Products!A2:E12,
         5,
         false
      )
   )
}

Or just

=arrayformula(
   vlookup(
      {Products!A2;Products!A9;Products!A6},
      Products!A2:E12,
      5,
      false
   )
)

Read our blog post to learn more about ARRAYFORMULA in Google Sheets.

Sheet with formulas

How to Vlookup on a range: ARRAYFORMULA and VLOOKUP

In a similar way, you can look up a data range:

=ARRAYFORMULA(VLOOKUP(search-data-range, range, column-index, [sorted/not-sorted])

For example, let’s search for the range A2:A6:

=arrayformula(
   vlookup(
      Products!A2:A6,
      Products!A2:E12, 
      5,
      false
   )
)

Sheet with the formula

How to Vlookup an entire row or several values: ARRAYFORMULA and VLOOKUP

Option #1:

Nest VLOOKUP with ARRAYFORMULA and specify indexes of the columns you want to have data extracted from.

=ARRAYFORMULA(VLOOKUP(search-key, range, {column-index#1,column-index#2,...}, [sorted/not-sorted])

If you want to return the results in a column, replace commas with semicolons between column indexes.

=ARRAYFORMULA(VLOOKUP(search-key, range, {column-index#1;column-index#2;...}, [sorted/not-sorted])

For example, let’s extract the entire row of "Denver sandwich":

=arrayformula(
   vlookup(
      Products!A8,
      Products!A2:E,
      {1,2,3,4,5}, 
      false
   )
)

or

=arrayformula(
   vlookup(
      Products!A8,
      Products!A2:E,
      {1;2;3;4;5}, 
      false
   )
)

Option #2:

If your row is much longer, like we have in the weather data set imported from a CSV file, you should use the COLUMN function in the column-index parameter.

=ARRAYFORMULA(VLOOKUP(search-key, range, COLUMN(data-range), [sorted/not-sorted])

Note: specify the data range within the COLUMN function excluding the last column with data. For example, your data range is A2:D. Your VLOOKUP function should contain COLUMN (A2:C), otherwise it will return an Error - VLOOKUP evaluates to an out of bounds range.

For example, here is the formula to return the entire row for the search-key 8/10/2020 8:00:00:

=arrayformula(
   vlookup(
      'Weather forecast SF'!B250,
      'Weather forecast SF'!B2:T,
      column('Weather forecast SF'!B2:S), 
      false
   )
)

Sheet with formulas

How to Vlookup to the left

The VLOOKUP function works in two directions:

  1. From top to bottom
  2. From left to right

If you need to look up a column to the left, there is a workaround:

=VLOOKUP(search-key, {lookup-column,range-without-lookup-column}, column-index, [sorted/not-sorted])

Here are some peculiarities to know:

  • lookup-column – specify the column to look up.
  • range-without-lookup-column – specify the data range excluding the column to look up. 
  • column-index – the number of the looked up column of the range-without-lookup-column from which the matching value will be returned. The index of the first column of the range-without-lookup-column is always 2.

For example, we need to look up the Price column for the value “11” and learn the Product name that matches it. Here is how the formula will look:

=vlookup(
   11,
   {Products!E2:E,Products!A2:D},
   2,
   false
)

Sheet with the formula

How to do a horizontal lookup: HLOOKUP function

HLOOKUP is VLOOKUP’s younger sister or brother. They even have similar formula syntax. The difference is that VLOOKUP searches first vertically and then horizontally; HLOOKUP searches first horizontally and then vertically. The function looks up the search-key in the first row of the data range, and then returns the value of the specified row.

HLOOKUP formula syntax:

=HLOOKUP(search-key, range, row-index, [sorted/not-sorted])
  • range – specify the data range on any sheet to look up. HLOOKUP looks for the search key in the first row of the range.
  • row-index – the number of the looked up row of the range from which the matching value will be returned. The index of the first row of the range is always 1.

This function works well with large-range data sets. For example, let’s look up "Relative Humidity" in San Francisco on 8/1/2020 0:00:00 (row #26). Here is the formula:

=hlookup(
   "Relative Humidity",
   'Weather forecast SF'!A1:Z,
   26,
   false
)

Sheet with the formula

How to vlookup from another spreadsheet: VLOOKUP and IMPORTRANGE

VLOOKUP itself works within one Google Sheets document. If you need to look up a data set in another spreadsheet, nest VLOOKUP and IMPORTRANGE. 

VLOOKUP IMPORTRANGE Syntax:

=VLOOKUP(search-key,IMPORTRANGE("spreadsheet-ID", "data_range"),column-index,[sorted/not-sorted])

Everything remains as is except for the range – you need to replace it with the IMPORTRANGE formula. Check out the VLOOKUP and IMPORTRANGE formula example, as well as other IMPORTRANGE capabilities in our blog post.

IF and VLOOKUP 

To customize your VLOOKUP formula result, you can nest VLOOKUP and IF differently. For example, here is the formula we used in the blog post “Trello Custom Fields to Google Sheets“:

={"Custom Field type","Custom Field name";
  arrayformula(
    if(len('All custom fields by cards'!E2:E)=0,,
      vlookup(
         'All custom fields by cards'!E2:E,
         {'All custom fields'!A2:A, 'All custom fields'!H2:H, 'All custom fields'!F2:F},
         {2,3},false
      )
    )
  )
}

The combination of ARRAYFORMULA, IF, and VLOOKUP allowed us to map Custom Field types by cards.

IF, LEN in the formula is a hack that allows you to switch off ARRAYFORMULA+VLOOKUP for empty cells. Without this hack, the formula would have worked with every row and returned #N/A for empty cells.

And here is how you can nest IF logic inside the VLOOKUP formula. Let’s say we have two data sets:

  1. Products and their prices now
  2. Products and their prices in 2019

We need to look up the price of "Italian Beef" depending on the year specified in the B1 cell. So, here is the VLOOKUP+IF formula:

=vlookup(
   "italian beef",
   if(B1=2019, 'Products 2019'!A2:E11, Products!A2:E11),
   5,
   false
)

The idea is that if B1 cell contains 2019, the VLOOKUP formula will return the price of Italian Beef in 2019. Otherwise, it will return the current price of the product.

Read more about Google Sheets logical expressions IF, IFS, AND, OR.

Sheet with the formula

To wrap up: is there any alternative to VLOOKUP?   

The combination of INDEX and MATCH is considered a better alternative to VLOOKUP. Those are Google Sheets lookup functions, i.e. they return the value based on a search key or offset number:

  • INDEX returns the cell value based on a specified row and column
INDEX(range, row-offset, column-index)
  • MATCH returns the position of a search key in a specified range (either a single column or a single row)
MATCH(search-key, range, [search-type])

To use the combination of INDEX and MATCH instead of VLOOKUP, you need to replace either row-offset or column-index, or both with the MATCH formula. It may look as follows:

INDEX(range, MATCH(search-key, range, [search-type]), column-index)

Or 

INDEX(range, row-offset, MATCH(search-key, range, [search-type]))

Or

INDEX(range, MATCH(search-key, range, [search-type]), MATCH(search-key, range, [search-type]))

If you want to discover more about this VLOOKUP alternative, comment below, and we’ll deliver a blog post dedicated to INDEX and MATCH. And if you need to export data from somewhere into a spreadsheet, check out a ready-to-use integration provided by Coupler.io. For now, Vlookup safely and streamline your data-centered activities. Good luck!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free