What is Excel VLOOKUP function (for dummies)?

Let me start with an example to explain what is Excel VLOOKUP function? If you know a little about VLOOKUP then move to examples section below. Suppose, you have a large excel table that stores products information in a departmental store. The information includes Product ID, Product Name, Price, Status, Category etc.

The table contains hundreds of records. Now, you want to find out the status or price of the Wheat bag quickly; how can you do that?

One of the ways is to “LOOK UP” the table information one by one and scan through your eyes (hope, you will find it soon without a headache) or use the VLOOKUP function – provide the known information like Product Name or ID and you are done. For example:

=VLOOKUP(“Wheat”,B5:F50,4,FALSE)

On that basis, let me define the VLOOKUP function:

  • As the name suggests, the VLOOKUP in Excel is used to “look up” or find a value in a table.
  • The “Wheat” is called lookup_value. You may use the text, number, a cell reference etc as lookup_value. The lookup_value must be in the first column of the table-array. In that case, Wheat must exist in the first column.
  • In our above VLOOKUP formula, the B5:F50 represents the table-array. The range/table-array must contain the lookup_value and the required returned value. For example, Wheat is the lookup_value and price is the returned value. So, VLOOKUP will search for our result in that table-array. You may use cell range, range name etc. to define table-array.
  • The 4 in above formula tells the column number where we are looking for returned information, the price in that case. So, the first column contains Product Names and 4th column contains the respective prices.
  • We used “FALSE” as the last parameter that specifies to find the exact match in the first column. If first column contains “Wheat” then it will return price. If it contains “Wheat bags” then there will be no match. If you want to find out the closest match then use the TRUE value. The TRUE is the default.
  • The V in VLOOKUP function stands for Vertical. That means the VLOOKUP function works for data organized vertically. Each row represents a new record.
  • VLOOKUP is case insensitive. That means “Wheat” and “wheat” are treated the same way.
This VLOOKUP function tutorial/guide applies to Excel 2016, 2013, 2010, 2007, Excel for Mac 2016, 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Starter 2010, Excel Mobile, Excel for Android phones

Now let us look at a few examples in this tutorial based on Products table for using the Excel VLOOKUP function to make things clearer.

The VLOOKUP example for getting the price

In this example, we will use the Product Name for the lookup_value and the function will return the corresponding price for that product. You may download the excel sheet for running this and other examples in your system or look at the graphic with each example.

The excel table contains the following data where we will use VLOOKUP formulas:

Excel VLOOKUP Table

The VLOOKUP formula for getting the price of “Wheat”:

=VLOOKUP(“Wheat”,C2:E11,2,FALSE)

VLOOKUP in Excel

You can see, we used the “Wheat” to lookup the Product Name column. As such, the Product Name belongs to C column while product names start from C2, so our range started from C2 to E11 in the VLOOKUP formula.

The price belongs to the D column which number is 2 in our specified range or table_array. So, VLOOKUP searched the Wheat in the left column and returned the price from the second column.

The example of using cell for lookup_name

In real scenarios, you may require entering any product name and VLOOKUP function should return the price of that product. Unlike the above example where we used constant literal “Wheat” in VLOOKUP formula.

In this example, you may enter any product name; press enter and the price of the matched product will display in the specified cell. First, have a look and I will explain a little bit how it is done:

VLOOKUP formula for any product:

=VLOOKUP(E13,C2:E11,2,FALSE)

The result as I entered Maze:

VLOOKUP cell

If you are running this example on your system after downloading the excel sheet then follow these steps:

  • Select the E14 cell and paste the above formula in “Formula bar”.
  • Enter any Product Name from the existing names in E13 cell and press enter.
  • As we specified E13 in the VLOOKUP formula for “lookup_name”, the VLOOKUP function will take that name, find the price and display it in the specified cell (E14).
  • Repeat the processes by entering other product names and check how it updates the designated price cell.

How to do VLOOKUP if a table contains duplicate entries?

If your range contains duplicate entries for the “lookup_name” then the VLOOKUP function returns the first matched value.

For demonstrating that, I made a few changes in the example table. The product “Rice” is entered twice; one with “In Stock” and the other with “Out of Stock” values in the Status column.  See the result yourself after using this VLOOKUP formula:

=VLOOKUP(E13,C2:E9,3,FALSE)

The output:

VLOOKUP duplicate

You see, even the second occurrence of Rice is “Out of Stock”, the Excel VLOOKUP function returned the first result only. The highlighted data in orange reflects the “table-array” in this example.

A demo of range_lookup as TRUE

Until now, we explicitly used the FALSE value for the range_lookup (the last parameter in VLOOKUP). The FALSE value means exact match as finding the lookup_value. Generally, you will use VLOOKUP function in that way. However, in certain situations, you may require finding the approximate match.

For a close match, you may use the TRUE value. This is also the default value of VLOOKUP function. So, if you omit this parameter, the VLOOKUP will find the close match.

Using the TRUE value assumes the first column is sorted alphabetically or numerically and then it will search for the closest value. If not sorted, this may result in unexpected results.

See the following example where I just entered “p-1” and see if it displays value for the closest match?

=VLOOKUP(E13,B2:E11,3,TRUE)

The result in excel sheet:

VLOOKUP TRUE

You can see, the Product ID is already sorted and it returned the p-010 price.

Tip: As TRUE is the default value, you should not omit this option. Instead, use the FALSE or 0 for the exact match.

Using a range name example in VLOOKUP function

In all above examples, we used the range of cells by specifying cell numbers. You may also use range names as using the VLOOKUP function. This may be particularly useful if you want to distinguish certain section of the table from others.

Have a look at this example where I used a subset of data from our Products table (C4 to E9). For that, I created a range named, limited_products and used this in the VLOOKUP function as follows:

=VLOOKUP(E13,limited_products,2,FALSE)

VLOOKUP range name

An example of using asterisk (*) wildcard in VLOOKUP function

The * wildcard can be used if you do not know the “lookup_value” exactly. The ‘*’ is used to match any sequence of characters. So, if we just enter the “su*” in our example sheet for VLOOKUP formula, see the outcome:

=VLOOKUP(“su*”,limited_products,2,FALSE)

The result:

VLOOKUP wildcard

Similarly, you may use the question mark (?) wildcard that matches any single character. For example:

=VLOOKUP(“su???”,limited_products,2,FALSE)

returns the same result as in above example. However, you must provide the exact number of unknown characters as the question mark. This would have resulted in “N/A” with two question marks:

=VLOOKUP(“su??”,limited_products,2,FALSE)

Tip: Rather than providing the hard-coded value in the lookup_name you may also use cell reference there like in above examples.

So, if our formula of VLOOKUP is this:

=VLOOKUP(E13,C2:E11,2,FALSE)

Enter the “su*” in E13 column will produce the same result:

VLOOKUP wildcard 2