How to use the VLOOKUP function in Microsoft Excel

Let's look at some Excel VLookup function examples and explore how to use the VLookup function as a worksheet function in Microsoft Excel:

Summary

The VLOOKUP function searching for a value in the first column of a table and retrieve the value in the same row in the index_num position. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. VLOOKUP function performs a vertical lookup. Lookup values must appear in the first column of the table, with lookup columns to the right.
The SUBSTITUTE function is a built-in function in Excel that is categorized as a Lookup & Reference Function.

What Does It Do?

It searches for the value you specify and returns a matching value from another column.

Return value

The matched value from a table.

Syntax

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

Parameters or Arguments

lookup_value - The value to search for in the first column of a table.
table_array - The range of data from which to retrieve a value.
col_index_num - The column number in the table from which to retrieve a value. The first column is 1.
range_lookup - [optional] Enter FALSE to find an exact match. Enter TRUE to find an approximate match (default).

Formatting

No special formatting is needed.

Usage notes

• Set range_lookup to (FALSE or 0) to require an exact match and (TRUE or 1) to allow a non-exact match.
• If you specify FALSE in range_lookup parameter and no exact match is found, then the vlookup function will return #N/A error.
• If you specify TRUE in range_lookup parameter and no exact match is found, then the next smaller value is returned.
• If range_lookup parameter is TRUE make sure that lookup values in the first row of the table are sorted in ascending order (top to bottom). else, vlookup may return an incorrect or unexpected value.
• If range_lookup parameter is FALSE, values in the first column of table do not need to be sorted.
• If col_index_num is less than 1, the vlookup function will return #VALUE! error. In case is greater than the number of columns in table, the vlookup function will return #REF! error.
• If you have data organized horizontally, use the HLOOKUP function.
• Do you want to look up a value based on multiple criteria? Use the INDEX and the MATCH function.
• Excel VLOOKUP is case-insensitive, meaning that uppercase and lowercase characters are treated as equivalent.
• Always use absolute cell references (with the $ sign) for the table_array argument to prevent it from changing when copying your formula to other cells.

Example

How to use VLOOKUP function in Microsoft Excel.

First Parameter
The first parameter is the value to search for in the table of data.

In this example, the first parameter is AD102. This is the value that the VLOOKUP will search for in the first column of the table of data.

Second Parameter
The second parameter is the table or the source of data where the vertical lookup should be performed.

In this example, the second parameter is B5:E9 which gives us to data to use in the vertical lookup.

Third Parameter
The third parameter is the position number in the table where the return data can be found.

In this example, the third parameter is 2. This means that the second column in the table is where we will find the value to return.

Fourth Parameter
The fourth parameter determines whether you are looking for an exact match or approximate match.

In this example, the fourth parameter is FALSE. A parameter of FALSE means VLOOKUP function is looking for an EXACT match for the value of AD102. A parameter of TRUE means that a close match will be returned.

Click here to learn more excel function