How to use VLOOKUP in Excel

VLOOKUP makes working with Excel con­sid­er­ably easier. You can save yourself the trouble of searching through a large data col­lec­tion manually and instead use the VLOOKUP formula to render results into another cell instantly. So that you can fully benefit from this Excel function, we’ll explain to you step by step how to do a VLOOKUP in Excel using a simple example as well as offer different ideas for how the function can be used.

What is VLOOKUP?

VLOOKUP is one of the most useful Excel features and belongs to the lookup and reference functions. Imagine you have a table con­sist­ing of several columns con­tain­ing data that’s been collected, and you need the right result for a certain search query. We’ll use a member directory as an example:

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 spread­sheet for the name. The function searches a column of the directory from top to bottom until it finds the name you’re looking for. Then it returns the cor­re­spond­ing value from the column where the phone numbers are stored.

However, VLOOKUP is limited in that the function only returns one result — namely the first one. If two people in the directory have the same name, the function will only return the number of the first person. In addition, the search criterion must always be in the column furthest to the left. As a result, the table must be struc­tured in such a way that no relevant columns appear to the left of the column where the search is being performed.

What is the syntax for VLOOKUP?

To ac­cu­rate­ly use VLOOKUP in Excel, you need to know how to enter the formula correctly. Every Excel function has a specific syntax that must be entered exactly as it is, otherwise the command will not return the correct result, or an error message will be displayed. The VLOOKUP syntax is as follows:

=VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)

Now, let’s take a closer look at the different pa­ra­me­ters:

• 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_array: This is the second argument, or piece of in­for­ma­tion required, when inserting the VLOOKUP formula. This argument refers to a group of cells that contain the source data and where you will be returning values from.
• col_index_num: This refers to the location of the column within the specified table location in numeric value. Note: Column D does not au­to­mat­i­cal­ly have the index number 4. If you start the data area from column B, then D would have the column index number 3.
• range_lookup: Should the results be exact or just ap­prox­i­mate­ly equal? With a 0 or FALSE only the exact match will be shown. 1 or TRUE will allow for other input and then return the next smallest value. The entry of this parameter is optional. If you leave this part blank, it will take the 1 function by default.

Here’s an example of how the VLOOKUP function in Excel looks:

=VLOOKUP("Turn";B1:D50;2;FALSE)

VLOOKUP example

So how do you use VLOOKUP exactly? The function is par­tic­u­lar­ly useful when it comes to searching large col­lec­tions of data and de­liv­er­ing a specific result. As an example, let’s imagine we have a music col­lec­tion 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 was added to the col­lec­tion.

Close

Find values with VLOOKUP

We want to find out which album is in inventory spot number 10. To do this, we can use the VLOOKUP function in Excel:

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

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

What if you want to carry out this search more than once? It is possible to merge VLOOKUP into a form that is also connected to the artists and format. For this, we don’t add the lookup value (the inventory number in our example) directly into the formula, but instead reference the cell where we want the result to be displayed:

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

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

Close

Combine VLOOKUP with other functions

Now, in most cases, the situation is the complete opposite of what we’ve shown in the examples above. You know which album you are looking for and would like to find the inventory number or artist. Using the CHOOSE function, you can get VLOOKUP to do this. This function allows you to es­sen­tial­ly change the table location. It does this by dis­play­ing VLOOKUP columns in a different place than where they actually are. In the CHOOSE function, you can specify different areas and the order that they should be in­ter­pret­ed in:

= CHOOSE(Index;Value1;Value2;…)

For the value parameter, we use the columns from our table and then specify a reverse order in the index. The index tells the function the order in which it should interpret the columns. The index spec­i­fi­ca­tion is done in curly brackets and the in­di­vid­ual numbers are separated by dots. CHOOSE replaces the table location parameter in our example:

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

Now the function for an album title gives us the ap­pro­pri­ate inventory number. It is also possible to expand the values in CHOOSE and record the complete data area. In our example, this is not necessary. We can use the inventory number to determine the other values using simple VLOOKUP functions.

Close