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 namedate of birth, and identification number, as shown below.

 ABC
1Employee NameDate of BirthIdentification Number
2Employee AFeb 20th 1995101010
3Employee BMar 12th 1987101011

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_numThe 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.

 ABCDE
1Item IDRegistered DateItem NameQtySales Price
210001Feb 20th 2019HP Desktop PC 1201500
310002Mar 12th 2019HP Desktop PC 2212000
410003Mar 12th 2019HP Desktop PC 3121460
510004Mar 13th 2019HP Desktop PC 4501260
610005Mar 12th 2019HP Desktop PC 5602100
710006Mar 14th 2019HP Desktop PC 6231670
810007Mar 16th 2019HP Desktop PC 7221875
910008Mar 18th 2019HP Desktop PC 8151980
1010009Mar 12th 2019HP Desktop PC 9131890
1110010Mar 18th 2019HP Desktop PC 1072050

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:

AB
1Employee IDEmployee Name
2e10011Employee A
3e10022Employee B
4e10033Employee C
5e10044Employee D
6e20011Employee E
7e20012Employee F
8e30031Employee G
9e30032Employee H
10e40014Employee I
11e40024Employee J
12e40025Employee K
13e40312Employee 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.

Leave a Reply

Your email address will not be published. Required fields are marked *