Faster with Excel: the VLOOKUP function explained

VLOOKUP makes working with Excel considerably easier. Save yourself the task of searching through a large data collection manually and instead use the VLOOKUP formula to render results instantly into another cell. So that you can benefit from the full practical benefits of this Excel function, we will explain to you step by step how to do a VLOOKUP using a simple example and offer ideas for its different uses.

Excel with Office 365 and 1&1 IONOS!

Use Excel to create spreadsheets and organize your data - included in all Office 365 package!

Office Online
OneDrive with 1TB
24/7 support

What is VLOOKUP?

VLOOKUP is one of the most useful Excel features and is one of the lookup and reference functions. Suppose you have a table consisting of several columns containing collected data, and you need the right result for a certain search query. For example: if this is a member directory you might know the name of a person but need to find their phone number. With the VLOOKUP formula you don’t need to search the entire spreadsheet for the name, as this function will do the job for you and automatically provide the phone number.

The name of the formula is composed of the “V” for vertical and the “lookup” function, although this only partly reflects its practical scope. Nevertheless, two important points can be taken from the name: the V indicates the direction of the search as the function starts at the top line and goes vertically downwards from there. Similar to the VLOOKUP is the HLOOKUP formula, which searches across rows horizontally. The referenced component is based on the referenced character: the function only refers to the value of another cell. Consequently, the VLOOKUP formula is related to the less-extensive REFERENCE function.

VLOOKUP: explanation of the syntax

To accurately use VLOOKUP in Excel, you need to know how to enter the formula correctly. Every Excel function has a specific syntax that you cannot deviate from, as otherwise the command will not return the correct result or an error message will be displayed. The VLOOKUP syntax is as follows:

                =VLOOKUP(search criterion; table array; column index number; match)

The parameters explained in greater detail:

  • Lookup value: this refers to the value that you are looking for. You can enter the criterion directly into the formula in words using quotation marks, or enter the specific cell that contains the data.
  • Table location: here you state the area where the lookup value is located. In Excel functions, a colon is always used to separate from-to characters.
  • Column index number: refers to the location of the column within the specified table location in numeric value. Note: column D does not automatically have the index number 4. If you start the data area from column B, then D would have the column index number 3.
  • Match: should the results be exact or just approximately equal? With a “0” or “FALSE” only the exact match will be shown. “1” or “TRUE” will allow other input and then returns the next smallest value. The entry of this parameter is optional: if you leave this part blank, it will take the “1” function as standard.

In practice, the VLOOKUP function can also look as follows:

                =VLOOKUP(“Turn”;B1:D50;2;FALSE)

Or

                =VLOOKUP(F2;B1:D50;2;0)

Note

If you want to enter a function into Excel by hand, go to the editing toolbar at the top of the spreadsheet and begin by typing an equals sign. This ensures Excel knows that you are entering a formula and not a word.

If you do not want to enter the formula by hand, you can also use the helpful “insert function” tool.  Here you will find the function from a list or by using a template. The tool will enter the correct syntax itself.

The lookup value must always be in the leftmost column. The search is then conducted in the columns to the right of it. Consequently, build your table so that no relevant columns appear to the left of the table.

Within the function you can also work with placeholders: by entering an asterisk (*) you are signaling one or more characters. The question mark (?) by contrast always stands for an exact character. These wildcards function within most other Excel functions.

A VLOOKUP example explained

So how to do a VLOOKUP exactly? The function is particularly useful when it comes to searching large collections of data and delivering a specific result. As an example, lets imagine we have a music collection and create a directory in Excel. Every album is given its own entry, although it is not sorted by name but simply by when it enters the collection.

We would like to find out which album is found in inventory spot number 10. To do this we can use the VLOOKUP function:

                =VLOOKUP(10;A2:D17;3;0)

The cell in which we entered the function should now give “Parallel Lines.”

Almost inevitably, you don’t only want to make one kind of search. So it makes sense to include VLOOKUP in a form that is also connected to the artists and format. For this, we do not put the lookup value – in our example the inventory number – directly into the formula, but instead give a reference to the cell where we want the result to display:

                =VLOOKUP(F3;A2:D17;4;0)

We put the same function in two more cells, but change the column index number to 2 and 4.

Now, the situation in most cases is the complete reverse to this: you know which album you are looking for, and would like to find the inventory number or artist. So you need your VLOOKUP formula to search to the left. One possibility to do this is to convert your table and move the columns. Doing this, you would then create a separate worksheet for each lookup value.

Alternatively, VLOOKUP can also be used to achieve this result by using another Excel function: “CHOOSE.” This lets us demonstrate VLOOKUP columns are located elsewhere to where they really are, allowing you to essentially change the table location. In the CHOOSE function, you can specify different areas and the order in which they are to be interpreted:

= CHOOSE (index, value1, value2, ...)

For the values, we use the columns of our table and then specify a reverse order in the index in which the function interprets the columns. The specification of the index is done in curly brackets and the individual numbers are separated by dots. CHOOSE replaces the table location parameter in our example:

= LOOKUP(F2;DIAL({2.1};A2:A17;C2:C17);2;0)

Now the function for an album title gives us the appropriate inventory number. It is also possible to expand the values ​​in CHOOSE and record the complete data area. At least in our example, this is not necessary: we can use the inventory number to determine the other values using simple VLOOKUP functions.

However, this formula only works if you enter an album as the lookup value; for artists or inventory numbers, the formula would have to be adjusted again. With a little extra effort, you can also build a drop-down menu by selecting the lookup value. Then use the IF function to adjust the table location in the VLOOKUP function, depending on your selection:

= IF(E2 ="Artist";LOOKUP(F2;DIAL({2.1};A2:A17;B2:B17);2;0) and IF(E2="Album";LOOKUP(F2;DIAL({2.1}A2:A17;C2:C17);2;0)))

So far, we've always worked with an exact match and so have always set the “match range” parameter to “FALSE.This is forgood reason: the example works mainly with text. The inaccurate match option works primarily with values ​​in a sorted list. If the value itself is not represented, the function uses the next smallest one. And we have such an ordered list of values ​​in column A. So if you are using VLOOKUP to look for a particular inventory number that is not yet assigned, the function will use the next smallest one – in our case the number 16.

=VLOOKUP(17;A2:D17;3;1)

VLOOKUP is also somewhat limited in that it provides only one result – the first. In our example, if we were not looking for a particular album but the artist and entering “The Beatles,” we would only get one result (“Help!”). With VLOOKUP, the problem unfortunately cannot be solved. For this we have to apply a very complex formula:

=Index($C$2:$C$17;LARGE(($B$2:$B$17=$F$2)*(ROW($B$2:$B$17)-1);COUNT IF($B$2:$B$17;$F$2) + 1-LINE(C1)))

More information about this formula can be found on this Udemy blog post on multiple values with VLOOKUP. The formula is a so-called array formula, meaning you do not just activate it with the enter key, but by combining [Ctrl] + [Shift] + [Enter]. This also creates the curly brackets that are typical of array formulas around the input.

Summary

Using VLOOKUP is always useful when dealing with large amounts of data. Popular uses for it are price lists and other kinds of directory. Setting up a search query is just one of many ways to use it: whenever you handle dependent values ​​from a table, the VLOOKUP function can make your job easier.

Excel with Office 365 and 1&1 IONOS!

Use Excel to create spreadsheets and organize your data - included in all Office 365 package!

Office Online
OneDrive with 1TB
24/7 support
1