Have you ever needed to use information from more than one table in your project? We introduce you to the PROCV function, which allows you to cross data within Microsoft Excel, providing the transmission of values from one table to another through a common field between them. The ability to cross-reference data between tables in Excel will provide you with interesting alternatives to increase your spreadsheets and manage information more effectively.
Knowing the PROCV formula
The PROCV (or VLOOKUP) function is intended to search for specific values in a given set of cells. To perform the task of crossing data between tables, it is necessary that the values are contained in each of them, so that a kind of identifier is determined, as we will see in the following example.
The syntax of the PROCV formula is represented as follows:
PROCV (lookup_value, table_array, column_index_num, [interval_search])
- Lookup_value: Should be common between the two tables, as already mentioned. It can also be a cell reference, selected with a click on the desired cell at the time of editing the formula.
- Table_array: Represents the set of cells in which the indicated value will be searched. The cells may be located in one or more columns.
- Index_num_num: The number that represents the column in which the information to be displayed is displayed. In a hypothetical situation, once you select two or more columns (for example: name, age, and weight), the column_index_num will indicate which of them the function will return.
- Search_interval: Optional parameter, it is used to define whether the search will be performed by exact values (which is recommended for text values) or approximate values.
Crossing data in practice
To demonstrate how the PROCV function will help us cross data in Excel, we have created an example with two country-related tables, located in different worksheets. In the first, we have the list of the ten countries with the largest number of gold medals at the Summer Olympics and the second, some geographic data related to these countries.
In the first table you can see that, besides the medal table, there is a column with empty fields, destined to receive information from the continents of each country. It is precisely these data that we will get in the second table, according to the following image.
To perform the data crossing, we will click on the C4 cell, which will receive the PROCV function. When you type the function, after the equal sign (=), Excel describes how to execute it and shows the path to its correct completion. To set the searched value, let's select the cell A4 (which in the table corresponds to the United States). The table array, which we have already explained indicates the location where the value will be searched, will be extracted from the second table, where we will select the range A2: C11, that is, all existing data with the exception of the header.
The next step is to define the "column_index_num". As we select three columns and the desired value, which represents the continents, is in the third column, we will indicate the number 3. Finally, in the attribute "search interval" we will opt for the exact match, informing the option "FALSE". Observe the result:
Selecting the cell C4 and copying its contents to the other cells of the column, we will have the continents relative to each country. As an example, we will indicate the number 2 as column index and so we will see that the capitals of countries are displayed. If we replace the name of the country (in this case we changed "United States" to "Soviet Union") we will verify that the corresponding capital has also been changed:
Check out the complete table with the steps performed in the example:
Cross-checking is very useful for a variety of situations (such as list checks) and allows better use of available data. What did you think of the tips? Are you in doubt? Get in on LUZ Excel Forum and send your question!