Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
VLOOKUP Function
2.1.
Syntax of the VLOOKUP function
2.2.
Arguments
3.
Using VLOOKUP in Excel
3.1.
Dynamic values in the table
3.2.
Wildcard Matching
3.3.
Multiple matches in the table
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024

VLOOKUP Function

Author HET FADIA
2 upvotes

Introduction

In Excel, we need to find the lookup values corresponding to a value in a table. For example, we may need to find a person corresponding to his id. Finding thousands of values in a table consisting of thousands of values becomes tedious.

Microsoft Excel provides a VLOOKUP function by which we can do this task easily and efficiently. We just need to call the function, and we can fill in Excel the corresponding values easily.

VLOOKUP Function

VLOOKUP stands for vertical lookup. VLOOKUP is an Excel function that helps us look up a value in a vertically organised table. VLOOKUP supports many features such as exact matching, approximate matching and wildcard matching. 

VLOOKUP is used to find the corresponding value in the table based on the given value.

Before learning VLOOKUP, let us first understand the syntax and parameters passed into the function.

Syntax of the VLOOKUP function

=VLOOKUP(lookup_value,table,column_index, [range_lookup])

The [range_lookup] is an optional argument in the VLOOKUP function

Arguments

1st argument lookup_value the value for which we lookup in the table

2nd argument table the table in which we want to look up the value. The table is represented as start_cell:end_cell

3rd argument column_index represents the index of the column in the table whose value is to be retrieved

4th argument [range_lookup] This argument is optional. It is given as FALSE or TRUE. TRUE represents the approximate match, and FALSE represents the exact match. When no argument is given, it automatically takes TRUE which represents an approximate match.

Using VLOOKUP in Excel

The purpose is to look up the table vertical. It searches based on the first index of the table and retrieves the column_index supplied in the arguments.

Consider the given table consisting of Hotel and Price.

We want to find the cost of Hotel Oberoi.

The syntax for VLOOKUP is =VLOOKUP(lookup_value,table,column_index, [range_lookup])

Now we are looking for the cost of the hotel at D3. 

So our lookup_value will be D3.

Lookup cell is D3

=VLOOKUP(D3,table,column_index, [range_lookup])

Now our table values start from A4 and end at B10.

Thus the table in the syntax will be replaced by A4:B10, as shown in the figure below.

We select the table, and thus the values A4:B10 will be shown in excel.

Table A4:B10

Now we want to retrieve the cost. The hotel names are in the 1st column in the table, and their costs are in the 2nd column. As we want the cost of the hotel, we must supply index 2.

col_index_num will be 2 because the values are in the 2nd column of the table

Now as the [range_lookup], we provide argument FALSE, which represents the exact match.

We then click enter, and the cost of the Oberoi Hotel will be shown in the current cell as shown in the below figure.

We get the cost of the Hotel Oberoi

Dynamic values in the table

Now, as we change the cost of Oberoi in cell B6, the value will also be automatically updated in cell E3. For example, if we change the value of cell B6 to 60000, the value at E3 will be 60000.

Thus VLOOKUP becomes very helpful when the values are dynamic in the table. 

Wildcard Matching

VLOOKUP also supports wildcard matching. Touse wildcard matching for the D4 cell we have to follow the below syntax

=VLOOKUP($D$4&"*",$A$4:$B$9,2,FALSE)

Here we use & “*” which allows the wildcards to be used in the D4.

Here * in the word “Obe*oi” represents one or multiple characters in place for *.

Thus “Obe*oi” would match for “Oberoi”, “Oberroi” etc.

Wildcard matching in VLOOKUP

Let us see wildcard matching for “L?ke Oberoi”.

The syntax for this would be 

=VLOOKUP($D$5&"*",$A$4:$B$9,2,FALSE)

The output will be 20000 as shown in the below figure.

Multiple matches in the table

The VLOOKUP returns the first match in case of multiple matches. 

For example, in the table, there are two Taj. In this case, the VLOOKUP returns the value of the first appearance of the Taj.

The output will be B4, i.e. 10000

FAQs

1. What is the VLOOKUP function?

 VLOOKUP is an Excel function that helps us look up a value in a vertically organised table. It helps us save time.

2. What are the advantages of using the VLOOKUP function?

The advantage of using the VLOOKUP function is that the change in the value of the table will automatically update the output of the VLOOKUP function.

3. What function is similar to VLOOKUP but used for horizontal tables?

The function HLOOKUP is used for lookup when a horizontal table is given. Another function XLOOKUP works for both horizontal and vertical tables.

4. What is the use of wildcard matching in the VLOOKUP?

Using wildcard matching, we can find the lookup values for partial words. For example, we can use Lake * to find a match for Lake Palace.

5. What does the VLOOKUP do when there are multiple matches?

The VLOOKUP function returns the value corresponding to the first matched result.

Key Takeaways

We have extensively discussed the VLOOKUP function in Excel.

We hope that this blog has helped you enhance your knowledge regarding the VLOOKUP function, and if you would like to learn more, check out our articles and archives on cyber security:

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. 

Enrol in our courses and refer to the mock test and problems available.

Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Learning!!!

Live masterclass