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 vertical 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 ?
= VLOOKUP(lookup_value, table, col_index_number, [range_lookup])
The 1st 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 2nd parameter of the function is the whole table in which you are going to look for the data.
The 3rd parameter is the number of the column within the table.
The 4th 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.
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.
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