## 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**,

**, and**

*date of birth***, as shown below.**

*identification number*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

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

### Remarks

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

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.

**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. The benefit of using **VLOOKUP **is that when you have a lot of information in a list, you can look up and retrieve the associated information very quickly and easily.