How to use the VLOOKUP function (with examples)

Do you want to learn how to use the VLOOKUP function in MS Excel? Read our tutorial and watch some examples for specific value, wildcard or using VLOOKUP with two sheets.

When to use VLOOKUP

The VLOOKUP function is useful when you need to find an information by row, within the same sheet or within another one, in a specific array of columns.

>>> Did you know that VLOOKUP stands for Vertical LOOKUP?

How to use VLOOKUP in Excel

The VLOOKUP formula in Excel includes 3 required arguments and 1 optional (range_lookup).

The VLOOKUP formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value – the value you want to look up.
It can be a specific value (“January”) or a reference to a cell (B2). Note that the value must be in the 1st column of the range of cells selected as table_array.
table_array – the range of cells in which the lookup_value and the return value you want to find are.
col_index – the column number where the return value is, within your table_array.
The 1st column within your table_array is number 1.
range_lookup – logical value specifying if you want the VLOOKUP function to find an exact match (0/FALSE) or an approximate one (1/TRUE).
If you don’t specify this argument, the default value is always 1/TRUE.

Tips when using VLOOKUP

– Make sure the return value is on the right side of the column you search from.
– We recommend using 0 as the range_lookup argument, to avoid unwanted results.
– You can use wildcard characters in the lookup_value argument if range_lookup is 0/FALSE and lookup_value is text. A question mark (?) will search for any single character and an asterisk (*) will match any sequence of characters.
– Return value will be 0 in Excel if the cell containing the return value is empty. If the cell containing the value you need is a formula, the return value will be the result of the formula.

VLOOKUP examples in Excel

Searching a value within the same worksheet – reference

In this example, we want to find the season (value to return) associated to the month mentioned as lookup_value; lookup_value is a reference (D2, D3, etc.).

Searching a value within the same worksheet – specific value

In this example, we want to find the season (value to return) associated to the month of August; lookup_value is a specific value (“August”).

Searching a value within the same worksheet – wildcard

In this example, we want to know the age of Jean-Paul but we don’t want to write his name entirely to save time; lookup_value is a specific value. We are using the wildcard *.

Using VLOOKUP between two sheets

In this example, we want to find the season (value to return) associated to the month mentioned as lookup_value; lookup_value is a reference. The data is on the sheet “data”.

The Excel sheet with the data we need:

The Excel worksheet with our VLOOKUP formula:

The VLOOKUP formula we used, explained: =VLOOKUP(A2,data!C:D,2,0).
A2 – the reference of lookup_value.
data!C:D – the value to return is in the sheet named data and the columns we need are columns C and D. In column C, we have the lookup_value and in column D the value to return.
Again, the value must be in the 1st column of the range of cells selected as table_array so we could not select data!A:D as table_array in this example.
2 – the value to return is in column number 2.
0 – we want an exact match.

If VLOOKUP is not working on your side, check our Notes section and if you need an Excel VLOOKUP training, feel free to join our Spreadsheet Course!

Also, you might have noticed that sometimes, the result of the VLOOKUP formula was N/A… Learn how to change this result for a nicer one by using the IFNA function.

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More
Split Semicolon Separated Value in Excel

How to split semicolon separated values in Excel

Split semicolon separated values in Excel into columns and rows, or convert columns to semicolon separated values, like an Excel Master!

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More