Microsoft released Excel back in 1987, and to no one’s surprise, the 34-year-old spreadsheet software is still going strong in the 21st-century workplace. From its compatibility with all of the major operating systems to its pioneering data features, Microsoft Excel has undoubtedly become an essential workplace tool across multiple industries.

If you’re looking to break into the business analysis field, brushing up on Excel to elevate your data management skills is exactly where you’ll want to start before you begin your job hunt. Because Excel has the ability to import, clean, extract, analyze, and report data, you can bet on answering a few Excel questions during any analysis-related interview

Microsoft Excel – Excel from Beginner to Advanced

Last Updated January 2023

Bestseller
• 254 lectures
• All Levels
4.7 (354,931)

Excel with this A-Z Microsoft Excel Course. Microsoft Excel 2010, 2013, 2016, Excel 2019 and Microsoft/Office 365 | By Kyle Pew, Office Newb

A good starting point to your analyst career journey is becoming familiar with some of the most popular Excel functions, like SUM, IF, LOOKUP, VLOOKUP, MATCH, CHOOSE, and FIND. These functions are not only super easy to learn but also staples in any job that involves data management. In this article, we’re going to take a look at the VLOOKUP function in Excel. (If Google Sheets is your preferred platform, don’t fret! We also have you covered.)

What is the VLOOKUP function?

To understand what VLOOKUP is and how to use it, you should first understand the name. The ‘V’ in the function name stands for vertical, which gives you “vertical lookup” when you put it all together. As the mashup term suggests, the purpose of the VLOOKUP function is to index match values of vertical table columns and return pieces of information from those vertical tables.

Vertical tables

You have probably already guessed that when it comes to vertical tables, the user arranges the data vertically with the columns representing different fields in the table. Compare this vertical arrangement to horizontal tables, where the user organizes the information horizontally. The difference is that the rows are now representing the table’s different fields.

Vertical Table            Horizontal Table

Whether using spreadsheets for business analysis or managing basic data about people, places, or things, most users tend to have a natural inclination to arrange their table data vertically. Because of this, VLOOKUP is a function that everyone would find very useful to learn.

The website Nature.com makes good use of vertical tables to present complex data to their audience in a clean and simple fashion. For the following VLOOKUP examples, we imported their vertically-arranged list of public transportation data into a spreadsheet. (Note that the examples in this article reference alphabetic data, but the same criteria hold true for any numeric or ASCII data.)

This is how the data shown on their webpage would look as a vertical list in Excel.

Formula builder

Now that we have a good picture of what a vertical list looks like in Excel, let’s find out how to use the VLOOKUP function with Excel’s Formula Builder feature. The images below show the Formula Builder section along with instructions for the VLOOKUP function. (When you’re viewing this in Excel, this will all show up as a single panel with the Formula Builder on top.)

As you see in the photo, the Formula Builder has input fields, a result field, and a Done button. If you are using a keyboard instead of a mouse, hitting Enter selects the Done button. The Tab key moves the cursor to the next field.

The result field on the bottom allows the user to test the function and confirm the resulting value.

Using VLOOKUP

Let’s take the definition apart so we can understand the elements of the function.

VLOOKUP looks for a value in the leftmost column of a table and returns a value in the same row from a column you specify. In our data, City is the leftmost column of the table:

This sounds simple enough, but let’s see how it works in action. Our first example will look at how many connections there are for a particular city. We will use the search value ‘Lisbon,’ and Connections will be the column we specify.

There are a few things to notice while using the Formula Builder.

Point and click

Each input field has a cursor selector icon at the far right of the field. This indicates the field is manageable with a mouse. Click in the input field, then click and select a range of cells.

In the example below, the highlighted data has a dashed line around it. The user drags the cursor to the right and down to identify the range of cells that hold the table data. Notice the Table Array argument is formatted as [top left cell]:[bottom right cell].

In our example, the first row is a header row of column names. Be careful not to include the header row.

Formula result

As you enter each field in the formula, it automatically calculated the result field at the bottom of the Formula Builder. It will display the default value of {…} until each field required by the formula is entered. If there is an error in the formula, this field will display the Excel error code #REF! and highlight the field that you need to correct with a blue box.

Pop-up errors

A pop-up error box will display if you select the Enter key or press the Done button before entering all of the required fields.

Function bar

The function is entered in the Function Bar across the top as you fill the fields in the Formula Builder.

Let’s return to our example of finding the number of public transportation Connections in Lisbon.

The Column field can be a little confusing. While Excel labels columns with alphabetic letters, Excel functions use a table index. The spreadsheet numbers the columns of the table data left to right, starting with 1. These are the Table Indexes used in functions. In our data, Connections is Col_index_num 5.

In the picture below, the cell highlighted in green is our target cell. This is the cell where you’ll enter the formula, triggering Excel to evaluate and display the results in the same cell. Notice how the value is the same value shown in the Formula Builder Result field. Formula Builder also displays the expression in the Formula Bar. Advanced Excel users often use the Function Bar to enter functions. The Formula Builder is helpful to debug complex formulas.

Did you notice the Range Lookup field has no value? The definition for that field is:

Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.

If the value is empty, Excel uses the default value TRUE. In our case, the closest match to our search value, ‘Lisbon,’ resulted in finding an exact match.

Let’s play with the Lookup Value and try to understand the impact of the Range Lookup field. At the start, we will keep the value set to TRUE and look for the closest match.

If the value is all lower case, we still hit on Lisbon.

If the Lookup Value is all upper case, again, we get a match with Lisbon.

Let’s go back to all lower case and change the Lookup Range to FALSE. Now we are looking for an EXACT MATCH, and the formula still returns a matched value for Lisbon.

This tells us that the VLOOKUP function is not case-sensitive.

Exact or closest match

Let’s play with a new search value to explore the Lookup Range field. The data includes many cities that start with ‘B.’ We will use these to explore the closest and exact match feature.

Exact matches

Here we have the results of Belfast for the Lookup Value, with the Lookup Range set to FALSE to get an exact match. For this test, let’s change the Col_index_num to 3 and search for the number of stops.

In the first case, the result is what we expected. We have an exact match with Belfast, and the number of Stops displayed is 1,917.

In this second case, the ‘t’ at the end of Belfast is missing, and the result indicates there is no exact match.

Closest match

Let’s look at some examples of searching for the closest match. These will have the Lookup Range set to TRUE. This is tricky and worth spending time on to understand.

Column H displays the formula result in the images below, and column J to the right shows the formula.

In the first case, the function is searching for ‘Ad.’ The first city of ‘Adelaide’ is greater than ‘Ad,’ and there are no cities less than ‘Adelaide,’ so Excel returns no match.

The second search for ‘Af’ evaluates ‘Adelaide.’ It is not a match, but ‘Af’ is greater than ‘Adelaide.’ Excel selects ‘Adelaide’ as the closest match and continues searching. Excel examines the next value in the list. ‘Belfast’ is determined to be greater than ‘Af.’ The search algorithm stops and returns ‘Adelaide’ as the closest value.

The next two examples are like the  ‘Ad’ search. Excel does not return a match because ‘Adelaide’ is greater than ‘Ac’ and ‘Aa.’

The next search is for ‘Adalaid.’  Excel doesn’t return a result because this value is less than ‘Adelaide.’ Like the ‘Ad’ example above.

Finally, we get a hit with the full value ‘Adalaide’ returning a match!

Now we know that all search values must be alphabetically greater than ‘Adalaide’ to ever get the closest match returned.

Let’s do a few exercises on data in the middle of the list. This time we will always get the closest match because the search values will be greater than ‘Adalaide.’

These next exercises play with a search for the closest value to Berlin. Notice that Excel returns the value associated with Belfast as the closest match until we enter the full text ‘Berlin.’ This is the same lesson we learned with Adalaide searches.

The next search tries to match with Bordeaux. Until we search for ‘Bordeaux’ or a value greater than ‘Bordeaux,’ the returned value is Berlin.

We are now seeing a pattern on how the search algorithm with VLOOKUP works.

Sorted data

As we’ve seen, the Range Lookup field plays a pretty significant role in the VLOOKUP function.  Let’s look at the definition again.

Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.

This time we will pay attention to the part that reads — sorted in ascending order.

The required sort order is ascending. You must apply this to the first column of the table data for it to work properly.

Let’s see what happens if we don’t sort the data.

In the example below, we reversed the sort, and all VLOOKUP searches failed. As we have seen, the VLOOKUP search starts at the top and always expects the next value in the list to be higher.  In this case, the highest value is at the top, and the search never goes any further.

Table selection and duplicate data

Using our public transportation data, we can search for data by Country. By sorting the table data by Country and excluding the City column, Country becomes the search column. Now we have duplicate data. The VLOOKUP function will only ever select the first of the duplicate Countries. The second duplicate will not be greater, so the search algorithm will stop.

Table access

There are a few additional methods for identifying data in lookup tables, including named tables and worksheet reference.

Named tables

Named tables allow the user to select the data and assign a name to it. Then the data is accessible by all functions with the name.

Select the data and right-click to reveal the context menu. Toward the bottom of the menu, select Define Name.

Excel displays the Define Name dialog. In our example, we will define the data table as PublicTransportationByCity.

The user can now use the Named Table in the VLOOKUP function to identify the Table Array. It is accessible in any spreadsheet. This allows users to have the data in one spreadsheet tab and the reporting in a second.

Worksheet reference

Another way of searching for data in a different worksheet is to reference the datasheet.

The Named Tables and worksheet referencing provides a valuable opportunity to separate reporting from the data.

There are many reasons why a user would choose to keep the data in a separate Excel file altogether. One person can maintain the data and another analysis. There could be a security issue and the data saved on a safe drive. The source data could be large, and this helps with performance.

Whatever the reason is, advanced Excel users have the ability to accommodate separate files. For example, if the external file has a named table, a VLOOKUP search would look like this:

VLOOKUP(“Lisbon,” [ExternalFile.xlsx]PublicTransportationByCity,3)

Conclusion

VLOOKUP is a powerful feature in Excel. It was one of the original functions added to the product, and many still use it extensively today. It is one of a handful of functions that enable a user to turn a spreadsheet into a feature-rich database tool.
Now that you understand VLOOKUP functions, you’re ready to move on to other Excel topics like pivot tables and IF THEN statements. Or perhaps you want to keep it in the family and learn about the other lookup and reference functions like FILTER and XLOOKUP. Whether you’re a true beginner or more advanced, Udemy has a ton of resources to get you on the path to mastering Excel.

Page Last Updated: July 2021