VLOOKUP

Excel Functions

Look up and retrieve a value in a table

Syntax

EXCEL

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

Arguments

Parameter	Description
`lookup_value`	The value to look for in the first column of a table.
`table_array`	The table from which to retrieve a value.
`column_index_num`	The column in the table from which to retrieve a value.
`range_lookup`	[optional] TRUE = approximate match (default). FALSE = exact match.

Return Value

A value from the given column number

Details

The Excel VLOOKUP function scans the first column in a table, finds a match, and returns a result from the same row. If VLOOKUP can't find a match, it returns a #N/A error or an "approximate match", depending on how it is configured. Because VLOOKUP is easy to use and has been in Excel for decades, it is the most popular function in Excel for basic lookups. You will find it in all kinds of worksheets in almost any business or industry. Although VLOOKUP is simple to configure, it has some default behaviors that can be dangerous in certain situations. The "V" in VLOOKUP is for "Vertical". The purpose of VLOOKUP is to look up and retrieve information in a table organized vertically: For example, with the table above, you can use VLOOKUP to find the amount for a given order like this: With the

Examples

VLOOKUP works with vertical data

With the order number 1005 as a lookup value in cell I4, the result is 125. VLOOKUP scans the first column of the table, matches order number 1005, an

EXCEL

=VLOOKUP(I4,B5:F9,3,FALSE)

VLOOKUP uses column numbers

To retrieve a value from a given column, just provide the number for column_index_num. For example, to retrieve the first name in cell H4, we use 2 fo

EXCEL

=VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email address

VLOOKUP has 2 matching modes

VLOOKUP has two match modes: exact match and approximate match. The last argument, called range_lookup, controls which match mode is used. The word "r

EXCEL

=VLOOKUP(value,table,col_index,TRUE) // approximate match
=VLOOKUP(value,table,col_index,FALSE) // exact match

VLOOKUP exact match example

The formula in H6 to find Year, based on an exact match of the movie title, is:

EXCEL

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

VLOOKUP approximate match example

In some cases, you will need an approximate match lookup instead of an exact match lookup. A good example is the problem of assigning a letter grade b

EXCEL

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

VLOOKUP has dangerous defaults

It is important to understand that VLOOKUP will perform an approximate match by default. This happens because range_lookup is optional and defaults to

EXCEL

=VLOOKUP(F5,B5:D10,3) // approximate by default!

VLOOKUP

Syntax

Arguments

Return Value

Details

Examples

See Also