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

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.

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:

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:

Or

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.

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

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:

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:

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:

So far, we've always worked with an exact match and so have always set the “match range” parameter to “FALSE.” This is for good 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 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:

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.