Let’s start this blog with the Vlookup function which is probably the most widely used function in Excel and determine whether it deserves its popularity or not.

The Vlookup function is part of Excel **references functions**. It performs a **v**ertical lookup by searching for a value (text, number or date…) in the first column of a table and then returns its corresponding value using the column chosen by the user.

## How does it work ?

*Syntax*

= VLOOKUP(lookup_value, table, col_index_number, [range_lookup]*)*

The 1^{st} parameter is the lookup_value i.e the value located in the first column and for which you want to have the corresponding value in other columns of the table.

The 2^{nd} parameter of the function is the whole table in which you are going to look for the data.

The 3^{rd} parameter is the number of the column within the table.

The 4^{th} and last parameter is the “type” of match. It is a “Boolean” i.e it can only have 2 values : True (approximate match) or False (exact match). In this article, we will only deal with the False case. We will see later in this blog how to make a relevant use of Excel references functions last parameter.

*Example*

Considering this table below :

=VLOOKUP(“Banana”;C2:E6;2;FALSE) would return $ **1**

=VLOOKUP(“orange”;C2:E6;3;FALSE) would return **5**

=VLOOKUP(G1;C2:E6;3;FALSE) would return **12**

=VLOOKUP(“Strawberry”;C2:E6;4;FALSE) would return **#N/A** because there are only 3 columns in the table selected by the user

## Good to know

As the example below shows, the vlookup formula is useful but has many cons:

- The lookup_value
**has to be in the first column of the table**, which is neither very flexible nor convenient. - When your lookup_value occurs
**more than once**in the first column of your table, vlookup will only focus on the**first**lookup_value found. - Used this way, the formula uses
**harcoded references**(Table : C2:E6 in the example, Number of column are both fixed). This is not a**common best practice in modelling**because it is not dynamic. Indeed, for example, what if a user decides to delete, insert or reorganize the columns of the table ?

However, the good point is that vlookup, like every Excel references function, is not **case sensitive**, which means that it doesn’t take into account whether your text lookup_value (or the text in the first column of your table) has capital letters.

There is the equivalent of vlookup for horizontal search … Hlookup. It basically works the same way but is less frequently used because spreadsheets tables are generally structured from top to bottom rather than left to right.

## So ?

This function is useful for basic searches but is **not flexible enough**. You can see **in this article** how to make Excel searches more dynamic combining Index/Match functions and using **relative references**.

Interested in this article ? Please feel free to like, comment and share ! My wish is to work with my readers and adapt my topics (for all levels) to your queries / questions !

Many more to come soon.

★ Beginner ★★ Intermediary ★★★ Advanced

Hi Franklin,

Wish you a good start and great success to your blog!

It’s a good idea to indicate the level of difficulty of the post… but sometimes it is really difficult to define. I am sure that VLOOKUP is easy to you, but not to many others, really depends on reader’s experience in using Excel.

Cheers,

LikeLike

I would rather use =INDEX() and =MATCH(). It is also faster than =VLOOKUP()

LikeLike

It is absolutely true and it is the topic of this article : https://excelside.wordpress.com/2016/02/19/the-powerful-index-match/

LikeLike

A useful formula!

LikeLike