# 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 Google Sheets add-on to automatically import data from different sources including Xero, BigQuery, WordPress, and many more. 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`

.

### 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.

### 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 )

### 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 ) }

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 ) )

### 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 ) )

### 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 ) )

### How to Vlookup to the left

The VLOOKUP function works in two directions:

- From top to bottom
- 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 )

### 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 )

### 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:

- Products and their prices now
- 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.

## 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