Whenever I see managers using Excel spreadsheets, it becomes clear to me the need to use PROCV. For those who do not know what this acronym is or does not have much intimacy with Excel, it is one of the formulas to search for information that Excel provides for its users and is very little used for looking "complicated".
When finished reading this post you will learn how to use the VLOOKUP function in Excel for any business situation.
If you're a little lost, rest easy, I'll show you some examples of using PROCV, present the function in full with a very detailed step-by-step and at the end of this post, I'll let you download an exercise worksheet with feedback.
Let's start by looking at some examples:
1. Example of use of PROCV in Employee Control Worksheet
Imagine you have a spreadsheet with a record of all your employees' information. Basically, it would have a similar view to our Registration and Employee Control Worksheet below:
Realize that all employees have a number of information registered such as your company's registration number, gender, date of birth, age, birthday month, etc. Now imagine that you have a list with 100 employees and every time you want to get the data from one of them will have a monstrous job both to find the data and to use it.
It is for these and others that Excel created the PROCV function (or VLOOKUP in English). With it, you can do automatic searches for information in your spreadsheet. In this case, we want to do searches for the name of the employees. Let's see how it works:
In our spreadsheet we created a new tab (3 Employee Query) where you can choose the employee name from a list in the D7 cell as indicated in the image above. From this choice, with the use of the PROCV formula, Excel automatically searches the employees' registration tab and takes all the corresponding information to the cells with the search formulas. In our case, we chose the employee Amanda Santos. Let's see how the result was:
So in this first example, to get the date of birth, the age and the CPF of our employee, we use the construction of the formulas in this way:
- For the date of birth:
= PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;4; 0)
- For the age:
= PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;5; 0)
- For the CPF:
= PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;8; 0)
Note that the 3 part of each formula (blue items) has different numberings for each item searched. This is because in our employee record tab, each of these information was placed in a different column, and this part of the formula matches the positions of such columns.
You should also have noticed that the name of our selected employee (Amanda Santos) has been reported in cell D7 (in red), so when the employees' names are changed in that cell, the formula is recalculated automatically based on the new name added.
As you can see, the VLOOKUP function is very simple to use and very functional. You should use it whenever you want to do vertical searches (in columns). It has a variation, the PROCH function, which searches for the horizontal (in lines). In this post we will focus only on the VLOOKUP function. To decide which of the two functions to use, observe in your spreadsheet how the values that may be the result of the search are distributed: if the values are in a column use the function PROCV, and if the values are on a line use the function PROCH.
Let's show another example of using PROCV.
2. Example of use of PROCV in Inventory Control worksheet
Now let's look at the example of a inventory control sheet. Here you will find all the merchandise information you have stored in your company. This is the view you would have of your products in stock:
See that all products, as well as in the Employee Master worksheet, have a series of data and information related to them, such as minimum stock, current stock, status, etc. If you want to see information about a certain product, just the VLOOKUP function (or VLOOKUP in English). In this case, we want to search for the name of the goods / products registered. Let's see how it works:
In our Inventory Control Sheet we created a new tab where you can choose the name of the product in a list in cell C7 as indicated in the image above.
From this choice of product, after using the PROCV formula, excel will search the Inventory tab, taking the information corresponding to the product to the cells that we created. In this case, we choose the pointing product. Let's see the result:
In this second example, to get the minimum inventory and stock quantity of the pointing product, we made the formulas in this way:
- For minimum stock:
= PROCV (C72. Inventory '! $ C $ 8: $ D $ 508;2;FALSE)
- For quantity in stock:
= PROCV (C72. Inventory '! $ C $ 8: $ E $ 508;3;FALSE)
As in our first example, the 3rd part of the VLOOKUP formula (items in blue) has different numbers, since in the Inventory tab, this information was placed in different columns. In addition, the 1st part of the VLOOKUP formula (items in red) is always the same, since it is the cell related to the value (name, product, etc.) that will be searched for.
You could still use Excel's PROCV formula to do searches and searches on almost any database you have. For example, you can get more information from sales order records, customer tables, for cash control, etc. Below you will see an example of various financial information that is automatically PROCV formula in our marketing plan worksheet:
According to the media chosen, the values of available budget, cost, revenue and result are automatically changed according to the values already added in other tabs of the Marketing Plan worksheet.
3. Understanding the PROCV function conceptually in Excel:
What is written when you enter the VLOOKUP function (vertical search) is PROCV (search_value, table_array, index_num_num, range)
search_value: it is the criterion used in the search, that is, it is the value to be located in the first column of the matrix of the table. In our examples, the reference items were employees (example 1) and products (example 2).
matrix_table: is the entire range of data, comprising rows and columns. It will always consist of two or more columns of data. The values in the first column of table_array are those used for the lookup_value. These values can be text, numbers, or logical values. In our examples, the table array was the databases where we put employee information (example 1) and products (example 2)
index_num_column: is the column that contains the value to be returned. The first column has a value of 1, the second 2, the third 3, and so on. In our example of the Employees worksheet, when we wanted to use the VLOOKUP formula to find the date of birth that was in the fourth column of the table matrix we used, we put the value 4 in num_index_column (which in our example was in blue). If this value is:
- Less than 1, PROCV returns the #VALUE! Error value.
- Greater than the number of columns in table_array, PROCV will supply the #REF! Error value.
interval: defines the accuracy of the search. It is a logical value that specifies whether you want the VLOOKUP formula to find an exact or approximate match. If it is TRUE or 1 an approximate match will be returned. If an exact match is not found, the closest major value that is less than the searched_value is returned. If it is FALSE or 0 (zero), PROCV will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first found value will be used. If an exact match is not found, the # N / D error value is returned.
Note: The value to be searched should always be in the first column, otherwise the VLOOK function will not work.
4. Now let's see the step-by-step practice in how to use PROCV
To do the step by step in the practice of setting up your VLOOKUP I will use the LUZ Employee Registration and Control Worksheet, but think of it only as the general structure to apply in your excel spreadsheet.
You will need to have a table structure, with at least 2 columns. The first column will always be your reference and from the second you will have the values that can be searched for. To be less abstract, let's look at the table in our worksheet. To get easier, we simplify to a table with 5 rows (non-account header) and 6 columns (name, date of birth, email, CPF, RG Dispatcher):
Any table that you have with information in rows and columns can be used for the PROCV function. Just remember to have your reference value as the first column. In this case we are presenting here, the first column is that of the employee's name.
2. Inserting the formula
The PROCV function searches for the occurrence of a text in a particular column and returns the value of another column (which you will specify in the formula) of that same row. In our case, we will create a form structure in another tab to insert the formula:
Note that in our structure, the reference value will always be the employee's name (first column of our database). Now that we have the structure, we can choose the employee (in this case we will use Carlos Carvalho as an example) and then insert the formula. For this, you will choose the cell corresponding to the value you want. Let's start with the birth date value (cell D8).
In this cell we will put the formula = VLOOKUP ($ D $ 7; '2. Employee registration'! $ B $ 3: $ G $ 7; 2; 0)
In this formula, we can see that:
- search_value = $ D $ 7 - which is exactly the name of Carlos Carvalho
- matrix_table = '2. Employees' tab $ B $ 3: $ G $ 7 - which represents the table that we show in the image above. The tab name appears in this formula because we are using it in another tab. If we were doing the formula in the same tab would just put B3: G7 here
- index_num_column - We put the number 2, because the date of birth is the second column in our database
- [search_interval] - We put 0 (zero), but could also be the value "false"
If you prefer, use the formula wizard. To do this, Click the tab Formulas> Insert function> PROCV. There, you will have these same 4 fields to fill:
When filling in the formula correctly and pressing the enter key, you will see that the birth date will automatically appear in the correct value:
3. The best way to use
If you continue to apply the VLOOKUP formula to the other cells (email, CPF, RG and Exp. Organ) we will have the table completely filled with all information. Don't be content with just one result, you can get complete information for a given information in a single tab or table as shown below:
- Pay attention to the values in the first column of table_array. Always check for possible left spacing errors, inconsistent use of quotation marks, date, or numbers as text or nonprinting characters. In these cases, the PROCV function may provide an incorrect or unexpected value.
- To make life easier for anyone using any excel formula, you can use the $ character to lock the formula and then use the drag (or copy and paste) cells function to not have the rework create the PROCV formula multiple times. I'll tell you more about how to do this in future posts
Give your opinion and suggestions
Tell me what you most want to know about excel. Write below in the comments what you think of this post. Like the examples? Did you get the PROCV formula on your spreadsheet? What other formulas and functions do you want to learn more about?
Test your knowledge
Download one now worksheet on the PROCV function with feedback.
We have some ready-made spreadsheets that use the PROCV formula:
Are you in doubt? Get in on LUZ Excel Forum and send your question!