## How to use the VLOOKUP function

*Article last updated on June 20, 2018*

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.

VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.

**Formula in cell C3:**

The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.

The third argument is which column you want to fetch the corresponding value from. In this example column 4 is entered and £30.00 is returned in cell C3.

The fourth and last argument lets you choose between approximate and exact match, in this case FALSE meaning EXACT match.

### Excel function syntax

**VLOOKUP(***lookup_value, table_array, col_index_num, [range_lookup]***)**

### Arguments

*lookup_value - *Value you want to look up

*table_array - *The range you want to use, remember that the VLOOKUP function always looks in the leftmost column in your specified range.

*col_index_num - *The column number which contains the return value

*[range_lookup]* - True or False (boolean value). True - approximate match, leftmost column must be sorted ascending. False - Exact match

**Table of Contents**

- Approximate match
- Related tables
- Dynamic arguments
- INDEX + MATCH
- VLOOKUP error
- VBA Example
- Using multiple conditions in VLOOKUP
- Download excel *.xlsm file

#### Approximate match

The fourth argument allows you to choose if you want APPROXIMATE or EXACT match. You want, in most cases, to use the EXACT match however the default value is APPROXIMATE match. So make sure you know what you are doing.

This example below shows you the difference between EXACT and APPROXIMATE match.

You must have your leftmost column sorted in an ascending order or you may get incorrect results.

Approximate match is useful if you want to find which group or range your value belongs to. The example above shows that value 158 is between 100 and 200 so the return value is 15.

**Formula in cell C3:**

Read more: Use VLOOKUP to calculate discount percentages

#### Related tables

A pivot table can't work with related tables (power pivots do) however the VLOOKUP function can quickly merge related tables so you then can analyze data in a pivot table.

These two tables share a data column (invoice), you can use the following formula to merge these two tables.

**Formula in cell E3:**

Copy formula in cell E3 and paste to E3:F15.

#### Dynamic arguments

This example shows you how to search multiple tables. The image below shows two excel tables, table10 and table20.

**Formula in cell E3:**

The INDIRECT function lets you use a cell value as the *table_array *argument, allowing you to search multiple tables.

#### INDEX + MATCH

The VLOOKUP function is that it can only look for a value in the leftmost column. If you don't have your values in the leftmost column you have two options, rearrange your table or use another method, shown below.

The INDEX and MATCH function allows you to search any column in a table and return a value in any column on the same row. This formula is so versatile that I actually prefer INDEX + MATCH over the VLOOKUP function.

**Formula in cell C3:**

The MATCH function lets you look for a value in a column and return the relative position of the found value in the array. AA-1611 is found on row 11 in cell range C6:C18

Read more: How to return multiple values using vlookup

#### VLOOKUP Error

The IFERROR function allows you to return a value if the VLOOKUP function returns an error, in this case NOT FOUND!

#### VBA Example

The following VBA code demonstrates how to use VLOOKUP in a macro.

The macro searches B6:B18 using the the value in cell C2 and displays the result in a messagebox.

VBA code Sub VLP() MsgBox Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:E18"), 4, False) End Sub

#### Download excel *.xlsm file

### Articles with the 'VLOOKUP' Function

The following 5 articles have formulas that contain the VLOOKUP function.

VLOOKUP with multiple criteria

The VLOOKUP function cell D16 looks for both a value in column B and another value in column C. […]

How to return a value if lookup value is in a range

In this article, I will demonstrate ways to lookup values that is to be found between given ranges and return […]

VLOOKUP of three columns to pull a single record

Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]

Use VLOOKUP to calculate discount percentages

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]

Choose between two data sets to VLOOKUP

Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]

### Functions in 'Lookup and reference'

The VLOOKUP function function is one of many functions in the 'Lookup and reference' category.

How to use the ADDRESS function

The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]

The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]

How to use the CHOOSE function

The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]

How to use the COLUMN function

The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]

How to use the COLUMNS function

The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]

How to use the FORMULATEXT function

The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]

How to use the HLOOKUP function

The HLOOKUP function lets you search the top row in a data range for a value and return another value […]

How to use the HYPERLINK function

The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]

Gets a value in a specific cell range based on a row and column number.

How to use the INDIRECT function

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

Identify the position of a value in an array.

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]

The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]

How to use the TRANSPOSE function

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

How to use the VLOOKUP function

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]

### One Response to “How to use the VLOOKUP function”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[…] https://www.get-digital-help.com/2017/07/11/everything-you-need-to-know-about-the-vlookup-function/ […]