Find everything you need in your spreadsheets using Excel’s VLOOKUP Function – WAU
Learn how to use Excel’s VLOOKUP function and see how to save time and eliminate errors when you need to search for information in your spreadsheets.
To use Excel with excellence, among the most important skills, is the knowledge – albeit general – about the functions of the program, the calculations they can do and the types of problems they help to solve. In this sense, it is worth knowing more about a specific search functionality: the VLOOKUP function.
Continue reading and find out what this function is, what its benefits are and how to use it in practice, with the help of a step by step. Follow!
What is the VLOOKUP function?
The purpose of the VLOOKUP function, or VLOOKUP for those who use Excel in English, is to find the value associated with some reference data within a table. Rest assured that we will explain.
Imagine that you have a very extensive table, with control of all the products you have in stock. It was built with 3 columns, where:
- the first has the name of the product (“Product”);
- the second has the quantity in stock (“Quantity”);
- the third has the unit value of that product (“Value”).
Suppose you want to know the number of items with the name “Sports shoes 1” in stock. What we could do with the VLOOKUP function is to go through the table in search of the product “Sport shoes 1” and return the corresponding value that is in the column “Quantity”.
In this case, the product name is the reference (also called the search value) and the quantity is the value you want to find (also called the return value). It is worth mentioning that, for PROCV to work, the search value must be on the left in the table in question.
That is, in our example, the function will only make one vertical search due to the occurrence of “Sports shoes 1” in the first column.
Another point of attention is that Excel will not consider repeated occurrences of the search value. Even though the term “Sports shoes” appears in more than one line, VLOOKUP will only display the return value for the first line it finds.
But that was just a simple example. Let’s see what are the biggest advantages of using the VLOOKUP function.
What are the main benefits of the VLOOKUP function?
We separated some benefits of VLOOKUP to inspire you to study the function more and learn to use it. Check it out below!
Think of a spreadsheet with many rows and columns. Manually finding the value related to a specific reference would be quite laborious.
Even if you used the “Search” function (“Ctrl + F” on Windows, or “Command + F” on Mac) and typed the reference, it would take time to see the desired value when browsing the columns. With PROCV, this process is a lot more agile and practical.
It is a fact that spreadsheets are a great tool for recording information and many companies rely on them to organize data relevant to the business.
It turns out that manual searches on very large tables are subject to errors, which can lead to wrong conclusions and decisions.
With an automated process like the vertical search of the VLOOKUP, you have more confidence in demand you need to do. With fewer errors, there will be less rework in your organization and less damage from failures that could be easily avoided.
Easy to unify data
Another use of PROCV is unify data from different tables. Suppose you have 2 tables:
- Table A: contains the name of the employees of the sales team and the revenue they generated in the month;
- Table B: contains the minimum sales revenue to be entitled to the respective commission (in percentage).
So, the idea is to fill the corresponding sales commission automatically. This is possible with PROCV, as we will see in more detail below.
People who read this content were also interested in:
PivotTable: step by step how to create amazing spreadsheets in Excel
How to put together a financial control spreadsheet that organizes spending
41 free online courses for you to take right now!
How to apply the VLOOKUP function in your spreadsheets?
Now that you know the usefulness of VLOOKUP and the advantages it offers, let’s see how to use the function in practice.
The formula is this, followed by the respective parameters:
= VLOOKUP (search_value; table_ matrix; column_num_num; [procurar_intervalo])
We explain each of these parameters below:
It is the search value with which you want to search. It is the term that the function will look for in the table.
It is the table on which you intend to do the research. It can even be on another sheet of the file.
It is the column number of the table where the return value is.
This parameter specifies whether you want the search to be made with the term equal to or approximate the search value. For exact match, you must enter 0 or FALSE and, for approximate match, 1 or TRUE.
Now, back to the example at the beginning of the post. Notice in the screenshot how we fill in the VLOOKUP properties to return the quantity for a specific product.
- sought_value: we define cell “B9”, which contains the search value we entered: “Casual sandal”;
- matrix_table: the range “A2: C7”, which comprises the table in question, starting in cell A2 and ending in cell C7;
- column_num_um: as the return value we want is in the second column (“Quantity”) of the table we defined in the previous parameter, the parameter here will be “2” (if we wanted the return value of the column “Value”, we should indicate this parameter like “3”);
- find_intervalo: we put the last parameter as “FALSE”, because we want the exact match of “Casual sandal”.
Back to the sales commission example, let’s see how the VLOOKUP applied to that scenario looks like.
- sought_value: we want to use the sales revenue as a reference value and the commission as the return value, so, as we are in line 3, we define cell “B3”;
- matrix_table: the table in question is Table B, so we put “E $ 3: F $ 9” (if it were “E3: F9”, without the “$”, when replicating the formula for the other lines of the “Commission” column of Table A, Excel would increment the numbers, leaving “E4: F10”, “E5: F11 ″,“ E6: F12 ”and so on);
- column_num_um: as the return value is in the second column of Table B, we put “2”;
- search_interval: here, we set it to “TRUE” for an approximate match, because the commission is only awarded when it exceeds a certain amount in sales.
Therefore, keep the VLOOKUP function always in mind when you need to do some research on your spreadsheets or unify data from more than one table. This method can be very useful, in addition to helping optimize your time and that of your team.
Did you like the possibilities of PROCV? Then, also see the 15 Excel formulas and shortcuts you need to know!