Excel VLOOKUP Function and Formula with Examples

How to use the VLOOKUP Function and Formula in Excel

In this tutorial, you'll learn how to use the VLOOKUP function and formula in Excel.

VLOOKUP is a function to look up for a value in a range of cells and returns some other information associated with the lookup value in the same row.

VLOOKUP is one of the most popular and well-known functions in Excel.

For example, let's assume that you have the demo employees' data that consists of employee name, date of birth, and identification number, as shown below.

	A	B	C
1	Employee Name	Date of Birth	Identification Number
2	Employee A	Feb 20th 1995	101010
3	Employee B	Mar 12th 1987	101011

Now, let's say you're looking for Employee A and other information where Employee A is the lookup value.

If found, VLOOKUP can return other information such as the identification number or date of birth.

In the above example, the identification number is 101010, and the date of birth is Feb 20th, 1995.

Now that you understand how VLOOKUP works. Let's get started and learn more about how to use the VLOOKUP function.

Excel VLOOKUP() Syntax

The syntax of the VLOOKUP() function is as follows:

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

Return Value

The matched value from a range.

Arguments

Lookup_value: The value that you want to lookup
Range: The range where you want to look up the value
Col_index_num: The column number in the specified range
Range_lookup: TRUE or FALSE
- FALSE for an exact match.
- TRUE for an approximate match

Remarks

Lookup_value must be in the first column of a range that you want to look up.
Range_lookup is optional.

You can use the VLOOKUP function to perform an exact or approximate match.

If you need to perform an exact match, specify range_lookup FALSE and TRUE for approximate matching.

This tutorial covers both exact and approximate matches.

VLOOKUP() Function Examples

Suppose you have the demo data, as shown below.

	A	B	C	D	E
1	Item ID	Registered Date	Item Name	Qty	Sales Price
2	10001	Feb 20th 2019	HP Desktop PC 1	20	1500
3	10002	Mar 12th 2019	HP Desktop PC 2	21	2000
4	10003	Mar 12th 2019	HP Desktop PC 3	12	1460
5	10004	Mar 13th 2019	HP Desktop PC 4	50	1260
6	10005	Mar 12th 2019	HP Desktop PC 5	60	2100
7	10006	Mar 14th 2019	HP Desktop PC 6	23	1670
8	10007	Mar 16th 2019	HP Desktop PC 7	22	1875
9	10008	Mar 18th 2019	HP Desktop PC 8	15	1980
10	10009	Mar 12th 2019	HP Desktop PC 9	13	1890
11	10010	Mar 18th 2019	HP Desktop PC 10	7	2050

Exact Match Examples

In this example, you will learn how to use VLOOKUP to perform the exact match.

Let's say you're looking for the sales price of item 10001 in a range of cells A2 through E11.

The VLOOKUP formula is as follows:

=VLOOKUP(10001, A2:E11, 5, FALSE)

VLOOKUP() found the lookup value 10001 on the list and returned 1500 as the sales price of the item.

Now, let's take the previous formula and update as follows:

=VLOOKUP(10001, A2:E11, 3, FALSE)

Here, we have changed col_index_num to 3, VLOOKUP() returns a value of a cell located in column 3 of the same row.

The formula returns HP Desktop PC1, which is the name of the lookup item.

Similarly, if you need to get the information about the registered date of the item, change col_index_num to 2 because the column number of column Registered Date is 2.

The formula is as follows:

=VLOOKUP(10001, A2:E11, 2, FALSE)

Partial Matching Examples

Remember that when you perform a partial matching in VLOOKUP, the lookup_value must be the text value.

Now, let's see some examples below.

Let's say you need to look up the sales price of an item, which name begins with HP, ends with 2, and any sequence of characters or words that come between are allowed.

The VLOOKUP formula is as follows:

=VLOOKUP("HP*2", C2:E11, 3, FALSE)

The item HP Desktop PC 2 is the only item that matches the specified condition in the above list.

The above formula returns 2000.

The following formula is another example of partial matching using VLOOKUP:

=VLOOKUP("*???????*P??3", C2:E11, 3, FALSE)

Here we've used both the wildcard characters (*) and (?) in the lookup value.

Note that the wildcard character (*) matches any sequence of characters, and the wildcard character (?) matches any single character.

The lookup value *???????*P??3 matches the following conditions in order:

* matches any word or phrase
?????? matches six characters in length
* matches any word or phrase
P matches character P
?? matches two characters
3 matches number 3

The formula returns 1460.

Approximate Match Examples

In this example, you will learn how to perform an approximate match using VLOOKUP.

The following list is our demo data:

	A	B
1	Employee ID	Employee Name
2	e10011	Employee A
3	e10022	Employee B
4	e10033	Employee C
5	e10044	Employee D
6	e20011	Employee E
7	e20012	Employee F
8	e30031	Employee G
9	e30032	Employee H
10	e40014	Employee I
11	e40024	Employee J
12	e40025	Employee K
13	e40312	Employee L

To perform an approximate match, you need to specify the range_lookup argument TRUE.

The following example shows an approximate match:

=VLOOKUP("e4001", A2:B14, 2, TRUE)

The formula performs an approximate match searching for Employee ID e4001.

If you look at the above list, you expect the formula will return Employee I at row 10 because e40014 is the best match in this case. However, the returned value is Employee H, which is the employee name located in row 9.

Sometimes, the VLOOKUP function does not perform an approximate match as expected.

In this tutorial, you've learned how to use the VLOOKUP function in Excel.