What Is VLOOKUP and How Does it Work in Microsoft Excel?

Microsoft Excel is the dominant product on the market for holding data and analyzing it. It is the main way to store, look at, and analyze data. This is true in tech, sales, accounting, and more. People who do data analytics on a regular basis come to love some tools and functions in Excel. Among the most popular is VLOOKUP. VLOOKUP is a way to find a piece of information in a spreadsheet.

Getting to know the basics of Excel

Before learning VLOOKUP, you should be comfortable with the basics of Microsoft Excel. You need to know how to open, setup, and save a spreadsheet. You need to understand what a spreadsheet, worksheet, and workbook are and how to work with them. You will also need to know about cells and cell references and how to work with cells. I recommend getting a grasp on the most important advanced Excel skills for professionals.

The VLOOKUP function is an Excel function. The rest of this article assumes you have some basic knowledge of Excel and how to use Excel’s functions and formulas. For a great introduction to Microsoft Excel, enroll in a Udemy course and join thousands of students who are learning to harness the power of Excel. You’ll find lessons that show you how to create and work with pivot tables, sort and filter data lists, and even share your workbooks via SkyDrive.

Excel formulas and functions

The real magic of Microsoft Excel comes from the formulas and functions. Learn to use the Excel formulas, and you will unlock the true power of Excel. The VLOOKUP formula will make finding information in large spreadsheets and worksheets as simple as one, two, three.

Excel stores data in the form of columns, or lists, of data. The Lookup, VLOOKUP, and HLOOKUP formulas allow you to sort through these lists of data. You can use them to find information that meets the criteria we are looking for.

What is VLOOKUP?

VLOOKUP in Excel stands for Vertical Lookup, or a lookup within a vertical. It is a built-in function in Microsoft Excel that lets you look for specific information. This can be really helpful in large sets of data. People who regularly work with large spreadsheets consider VLOOKUP one of the most helpful Excel functions.

For example, say you have a giant spreadsheet with lots of information about each item. This might include quantities, sales, prices, storage bins, and more. You want to know the amount of only one item in that inventory. With VLOOKUP, you can look up only that price. Let’s say you have a list of furniture for sale. You want to know how many of one type of couch, called an Ava couch, are in inventory.

Every bit of the information about every piece of furniture is together in one table. The vertical part of VLOOKUP means that it only searches for information in one column of the table. It searches through that one column to find the term you are looking for. So in this example, it searches the “items” column for the Ava couch. Then it goes and looks in a second column, quantity. That tells you how many of the Ava couches there are. The VLOOKUP function returns the amount.

Where is VLOOKUP?

There are two ways to access the functions and formulas in Microsoft Excel. First, if you know the formula name and how the formula works, you can type the name of the formula and requirements directly into the cell where you want the answer to appear.

If you do not know a formula’s requirements, you can choose the desired formula from the list. Once you have selected it, a formula wizard will help you complete the formula. To access the formulas in Excel, you need to select the Formulas tab on the ribbon.

The VLOOKUP formula is part of the Lookup and Reference formulas. Select the “Formulas” tab in the top menu in Excel with your spreadsheet open. Then select “Lookup & Reference.” A menu will drop down that is alphabetical.

Click on the “VLOOKUP” formula. This will launch the formula Builder on the right.

You can also enter a VLOOKUP manually. We will go over how to do that when we discuss how VLOOKUP works.

How does VLOOKUP work?

The best way to understand any formula in Microsoft Excel is to try it out. We will work through one example here. You can find another Excel VLOOKUP example in this article. For this sample, we will create a set of fictitious sales data. This is so that you can see how the Excel VLOOKUP function works and how you can use this function in your worksheets. Our fictitious data looks like this:

You can also get sample data to practice with — like wholesale vegetable prices — from sites like data.gov. Excel also offers data validation.

Elements of the VLOOKUP formula

The VLOOKUP formula includes several elements. These include the “Lookup_value,” the “table_array,” the “Col_index_num,” and the “Range_lookup” arguments. If you use the Formula Builder, it will prompt you on each part of the VLOOKUP formula. If you enter it manually, it will look like this:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Each of the bolded parts is an element where you will have to fill in that data. Let’s take a look at what each of these elements means and how they work.

The lookup value

The lookup value (Lookup_value) is the value you are searching for in the list of data. Note that to search the data, you need to arrange the data. You need to make it so that the value you are searching for appears in the first column. Then make sure to sort that column numerically. To search for a kind of fruit, use the formula wizard to select the fruit you are searching for.

The formula wizard will update the field, so when you select the area, you will see that the formula will now search for “Apples” within the list of data.

Table array argument

Now that we have told Microsoft Excel what we are searching for, we need to tell Excel which table or list of data it must search. This is the table array (Table_array) argument. To tell Excel to search your data, select this argument. Then select the entire table of data you wish to search. Next, press enter to select that data.

Now we have told Excel what to search for and where to look for the information. Now we need to tell Excel what data we need from that item. In this example, we will use the function to find the price of that item.

Col index num argument

Col_index_num is short for column index number. That is the number of the column we want to search. We need to tell Excel what the column number is that contains the data we are searching for. To work out the column number, count from left to right. Then enter the column number that contains the data you want Excel to return. 

In this case, we want to return the item price, which is column number three in our table. So we enter the number three in the argument wizard.

Range lookup

The range lookup (Range_lookup) is a true or false value for either finding an exact match or a close match. Microsoft Excel allows you to search for an exact match or the closest match. In this case, we want an exact match. That means you will enter “true” into the last argument. If you were looking for the closest match, you could enter false. If you enter false, Excel will find the closest match instead of an exact match. This is also called an approximate match.

The range lookup is the only optional input. If you don’t enter anything, Excel will default to what it thinks the logical answer is.

You will see that the formula wizard actually shows you what the answer to the formula will be. You can use this feature to make sure you have entered the formula correctly and that it is working properly. In our case, you can see that this formula will return a value of $1.40. That is the price for apples, so our formula is working correctly.

Once you are happy with the formula, you can click enter.

If you wanted to enter this function manually, it would look like this:

VLOOKUP(A3, A1:F11, 3, TRUE)

More with VLOOKUP

There are some cases not covered in our example. What happens if the data you are looking for is not there? What can you do to broaden the range of what you are looking for?

#N/A error

If VLOOKUP does not find the data you are looking for, it will return an error instead of the data. This error will look like “#N/A” in your result. This means the information is not available. 

For example [example].

If you see this error, you can check if you made a typo or other mistake in what you were looking for. You can create a response to give in the cell if the data is unavailable. You can do that with the IFNA or IFERROR functions. The latter will cover all kinds of errors. To set these, look them up in formulas or enter them manually.

The IFNA, or function for if you get a result of NA, for a VLOOKUP for our example formula would look like:

=IFNA(VLOOKUP(A3, A1:F11, 3, TRUE), “Not Found”)

This would return “Not Found” in the cell if an NA came up, but it would not cover any other kind of error.

And the IFERROR formula, for if there is any kind of error in your VLOOKUP for our example formula would look like:

=IFERROR(VLOOKUP(A3, A1:F11, 3, TRUE), “Search Error”)

This would return “Search Error” in the cell for any error. You would not know what type of error it was. It would cover every kind of error though

You can also try to prevent results not turning up by expanding your search. You can use wildcards to expand it.

Wildcard values

Wildcards are characters you can put in your search to include more results. By adding a question mark or asterisk, you have Excel look at more. 

The question mark, ?, means there is a character here, but I do not know what. That could be a letter or a number. For example, say you want to include a bunch of items with different numbers but the same basic name, like Couch 1 Greta, Couch 2 Greta, and so on. You can search for “Couch ? Greta,” and the search will include each one of these. Or you know you are looking for a name that is three letters long and are not sure what it is. You can use “???” and search the names column to find it. This will go in your lookup value.

The asterisk, *, means add more here. An asterisk can go at the beginning or the end. So in our furniture names example, say you want a chair but cannot remember what name you gave it. You know all furniture starts with the type of furniture. So you search the items column for “Chair *” and get all chair names. This will also go in your lookup value.

Moving forward with VLOOKUP and more

Now that you know how to use VLOOKUP, you can speed up searches and handle large amounts of data. This will make you better at any job or task where you are dealing with large spreadsheets. You can make better use of your time, impress your bosses, and make the spreadsheets you show clients easier to use and understand.

While VLOOKUP is perhaps the most popular function for data analysis, it is not the only one. The more you learn in Excel and with its formulas and functions, the better you will be at using it. Expertise in Excel can open up new doors for better positions and new careers. Don’t stop here!