## 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!!!