What is VLOOKUP in Excel: A Complete Guide

MS Excel is one of the most common and popular software programs data experts use. Along with being a spreadsheet software program, MS Excel is also an analysis tool and a powerful visualization tool, making it a popular choice with data analysts globally. MS Excel is a huge task, including various functions and formulas. VLOOKUP in Excel is one such function that data analysts often use to search information from a table consisting of vast volumes of data. If you want to learn MS Excel, learning the VLOOKUP formula is essential. There are abundant VLOOKUP examples available online and offline, from where you can have a fair idea regarding the function. 

Learn data science courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Here we will talk in-depth about the VLOOKUP function in Excel and how it works.

Understanding the basics of the VLOOKUP function in MS Excel

The complete meaning of VLOOKUP is ‘Vertical Lookup’. When you are looking to search for some specific information in the spreadsheet, you can do it easily with VLOOKUP in Excel. Finding information from a simple and small spreadsheet is easy. But when the spreadsheet is complex, having enormous volumes of data, VLOOKUP helps find the data in no time. You just have to put the right VLOOKUP formula to get the result instantly. 

When you look at VLOOKUP examples, you will see that the function carries out a vertical lookup when it searches for a value in the table’s first column, and it returns the value in the same row in the index_number position. This is a premade function in MS Excel and is written as =VLOOKUP. 

VLOOKUP Formula in MS Excel

The VLOOKUP formula in MS Excel looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

If you want to explain this to a layman, it is like “you have to search for a piece of information, in this particular area, and give the corresponding data/information from another column”. 

Another way of explaining this goes like VLOOKUP (value to be found, table reference, pulling a matching and similar value from the nth column of the table, closest/exact match).

The arguments in the VLOOKUP formula and function

When you go through VLOOKUP examples, you will come across many new terms. These are called arguments in technical language. To understand VLOOKUP in Excel well, you must first have a clear idea about these arguments. 

We will discuss these arguments here:

• Lookup_value 

This argument is required in any VLOOKUP formula. Lookup_value points toward the value you want to look up in the first column of a table. 

• Table_array

This is also a mandatory argument needed in the formula. The table array is the data array that you have to find. In the VLOOKUP function, the search starts from the left-most column of this array. 

• Col_index_num

No VLOOKUP function can work without this col_index_num argument. This is an integer. This specifies the column number of the given table_array, which you want to return a value from. 

• Range_lookup 

This is an optional argument. This argument defines what result the function will show if there is no exact match to the lookup_value. You can set the argument to TRUE or FALSE. The meaning of TRUE or FALSE is as follows:

TRUE

If the argument is TRUE, it is an approximate match. An approximate match occurs when an exact match doesn’t exist. In such a case, you have to use the closest match below the lookup_value.

FALSE 

If the argument is FALSE, this shows the exact match. If it cannot locate an exact match, it will return an error. 

How do you use VLOOKUP in Excel?

Before you start using VLOOKUP in Excel for your work, it is better to check out some VLOOKUP examples so that you have a clear idea about how things work. We will also provide a step-by-step guide to using this function. 

Step 1: Organizing the data

To use the VLOOKUP formula effectively, you must ensure that the data is well organized. The provided data should also be suitable for using the formula or function. 

VLOOKUP functions in left to right order. Therefore, the information you need to find should be to the left of the corresponding data you want to extract. The arrangements of the columns are an important factor for the seamless functioning of the VLOOKUP formula. 

Step 2: Telling the function what to lookup

In this second step, you instruct Excel on what to find. So, you have to type the formula “=VLOOKUP(“. Next, you select the cell containing the information you want to find. 

Step 3: Telling the function where to look

In the third step, you have to select the table where the data is located. Next, you instruct Excel to search the leftmost column for the data selected in the previous step (Step 2). 

Step 4: Telling Excel which column to output the data from

In this step, you need to tell MS Excel the column containing the data you need as an output from the VLOOKUP formula. Excel will need a number corresponding to the column number in the table provided to carry out this work. 

Step 5: Finding the approximate or exact match

This is the final step of using VLOOKUP in Excel. Here you have to tell Excel whether you are looking for an ‘approximate’ or ‘exact’ match by entering ‘TRUE’ or ‘FALSE’ in the formula.

An approximate match proves to be very useful when looking for a figure which might not be there in the table. In such a case, Excel will look for a number closest to the given figure, even when the given figure is not present in the dataset. With this, you can prevent mistakes in the VLOOKUP function and formula. 

Important pointers to remember about the VLOOKUP formula and function

Now that you know the basics of VLOOKUP in Excel, here are some crucial pointers that you must always remember:

• In VLOOKUP, it always looks to the right side. This is because the data it fetches is from the columns to the right of the first column in the given table. 
• When you omit range_lookup, you will get a non-exact match with the VLOOKUP formula. But if there is an exact match, that will be displayed. 
• Case-sensitiveness doesn’t work with this formula.
• If duplicate values exist in the lookup column, VLOOKUP will only match the first value. 
• VLOOKUP allows wildcard usage like an Asterix (*) or a question mark (?).

Conclusion

VLOOKUP in Excel is probably the most popular function and has its unique advantages and disadvantages. The greatest benefit of this function is that it is simple and easy to decipher. VLOOKUP will scan the data table, find a match, and show a correct result. From beginners to experts in MS Excel, use this function for different jobs. Moreover, there are abundant VLOOKUP examples available for your quick reference. 

upGrad offers certificate programs in Data Analytics 

The demand for data analysts is on the rise in the industry. If you are interested in such a profile and want to make a successful career in this domain, we recommend you to join upGrad’s Data Analytics Certificate Program. Fullstack Academy powers the program. 

The program duration is nine months, with 12-14 hours of class per week. A world-class curriculum has been designed for the program, including topics like SQL, Python, and Tableau, along with ML, NLP, and Artificial Intelligence. Candidates will learn Python, SQL, and data visualization skills. World-class faculty is available for the program with the inclusion of Tableau and AWS Certification preparation. The program also offers guaranteed job opportunities in multiple designations. Apply now and book your seat today for the next class!