Learn how to cross data in Excel and make your work more efficient

57
57176

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.

Formula syntax

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!

previous articleCost Accounting: What it is and how to do it
Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

1. Hi Elcio, probably the problem is in using \$ to make the array fixed by dragging, you're causing the references to change and this causes an error in your result

2. Good afternoon
I copied the spreadsheet just like the blog.
It worked out even the first formula that is to bring from the 2 table the name of the continent of America.
Then to copy below the other continents gave dick, # n / d

3. Hi Caesar, your explanation was a bit confusing. In general, PROCV always works by counting columns to the right. So if your data validation list is of codes, you will have no problem, when choosing a code, you will have the corresponding product response if you have written the formula correctly. If you want the opposite (choose a product and see your code), you need to create an auxiliary column by replicating the codes on the right side of the product names column. Then you will still have to create an SE to identify whether you are using the code or the name to search dynamically

4. I need help I am making a spreadsheet where column A has numbers of codes and in the b names of the products, being that I am wanting to put a formula for ex. procv, when to put the cod. it brings the name of the product. but would be using validation in data in list format as well. you could help me???
obs. if I put procv to search from the product column it brings me the cod. more if it is the reverse, nothing.

5. Hello Monica, I did not quite understand the structure of your spreadsheet and what you want to do exactly. Of all the forms, surely it is possible to organize the information to be thrown by the savannah of budgets, everything will depend on formulas or code VBA

6. Hi Olindo, you can have a list with the number of children per voter and then, when clearing the votes, generate a multiplier that searches that list by PROCV

7. Good night!
I need to make a list of names of students who will vote in an election for director of a public school.
I need to find out which students have the same affiliation, that is, when the parents or guardians vote, we will know if they are voting for more than one student or not. Ex: A mother or father may have 1 or more children in the same school.
I await a suggestion.

8. Good afternoon, I have the following project: it is the design of an annual budget that will modify balances as the year progresses, that is, every month (12) you have: | \$ initial | reduction | extension | available 1 (initian-red + amp) | compromised | paid | available 2 (dispo1-com-pag) | \$ month | \$ initial accumulated (disponib2 + month) | .
in another table record all reductions, all extensions, all committed and all payments. so that I capture one that serves me for other records and I empty into another that only moves the money.
the identical columns can be: budget key - month - movement (reduced? payment?) and there with those 3 matches I know where to empty the information.
Is it possible that at the time of capture on a basis, in automatic pull the amounts to the budget sheet?
I really appreciate your help, please if I can provide my mail and advise me I will thank you very much

9. Rafael good night, I do not know if you can help me, I have a database that has the data of benefits granted per month: name, policy, benefit granted, date of entry, date of discharge. And there is another database that is generated when the insurer's platform is added to the benefits granted for the financial recovery of each case, it generates a folio per case raised and allocates the amount to be paid. How can I complement the first database with the assigned sheets and amounts for each case, since there may be a beneficiary with a policy number (which in addition to the name is the data in common between the two databases), with different benefits.

10. Hi Bruno, I need you to explain a little better what you want to do and what is in this table

11. Hello Rafael, good afternoon! I have a table in which the first column is date, using procv I can not get into the result of the first column. If I move the date column to any other position in the table, I get the result, only in the 1 position I'm not getting. Could you help me with the resolution? Thank you very much !!

12. Hi Victory, it seems to me that using the function SE with AND you will succeed. It would be something like = SE (D = "Yes"; B; SE (D = "No"; 0; "Whatever you want"))

13. Hello Rafael, my name is Victoria Chris and I am currently doing a job in excel with a friend, however, we are faced with a question and, if possible, I would like your help. The situation is as follows:

I have a "B" cell that shows different values ​​as I modify a given condition in a drop-down list of another "A" cell.
I would like to make the value shown in cell "B" appear in a third cell "C", but at the same time, I would like to cause the value in cell "C" to change to zero or return to the previous value shown,
as I select in another drop-down list cell, cell "D", the variables "yes" or "no" in this.

I know that for values ​​in cell B to appear in cell C just link normally, however for the above purpose, obviously a simple link does not work.
I did some tests with several formulas and I could not do what I wanted.

14. Hi Cris, I did not quite understand what you did and what you need. Can you explain a little more? In general, the operation of PROCV is as follows:
1 - you have a table with lots of data (let's assume that the first column is the name and then you have phone, address, etc.)
2 - in another tab (or in the same), you want, according to a name you type, already see the phone, address, etc of that name
3 - In this case, you use PROCV referencing the cell where the search name will be written and using the 1 table as the search array

15. Rafael, good afternoon! I'd like some help. I have a spreadsheet and I do not know if this is correct, linking one cell to several cells. I have PROCV on plan1 with the information from plan2.

16. Hi Daniel, you did. For this you only have to create a SOMASE that adds all values ​​related to restaurants. Since you have a tab for each month, you do not even need to use SOMASES

17. Hi Erfaim, you just create a simple reference to the cells that you will fill. Supposing you fill in A1, A2 and A3 and have the duplicate in the D1, D2 and D3 cells. In this case, in D1 you would put = A1, in D2 = A2 and in D3 = A3

18. Hello good afternoon,
I am creating a two-way note of commitment on the same page, but I wanted the second one to fill in automatically when I was completing the first route. give me a light please

19. Hi Rafael,
The post helped me but I still can not solve my problem. I am structuring a spreadsheet for family expenses. I have tabs with the monthly invoices invoice of nubank (mine and my wife, in separate tabs), with values ​​in categories. I have a tab to manage the expenses and I want to pull the data of each expense from the tabs of the invoice and put in the cell of the management tab of the respective month.
Example, in June I had 3 spent in Restaurant, I want these expenses to be added in a cell in the managerial tab that represents my June gestures in restaurant.
I hope I have managed to explain.

20. Hi Felipe, the error is in the formula. Explaining PROCV - In it you will use 4 values:
1 - value searched - must be the cell with the student's name
2 - array - table where the name will be and the other data as well
3 - column index - number of columns to the side - in this case, you should probably at least use the 2 value (in your case you have placed 1)
4 - FALSE or TRUE - Must use false for exact matches

21. Greetings Rafael,

I've raised customer data and billing for years and would like to cross-reference this data to see the names that appear on one sheet and do not appear in another. So, knowing which names are gone, I can identify my inactive clients. This case seems somewhat different from the example cited. Does the formula described here apply to this case?

Thank you for your help and I congratulate you on your solicitude!

22. Hi Rafael,
I work in a state school and in doing the historical we always use the names of many of the same schools from which the students come. I'm trying to do this cross-reference so I do not have to always type the same names and thus pull the data from another worksheet. When I finish the formula (seemingly correct) it looks like # N / A.
This is the formula: = PROCV (MODEL! H50; Plan1! A2: A11; 1; FALSE). I already inverted the data of "model" and "plan1" but I did not obtain result.

Thank you.

23. Hi Israel, this seems to me the case of using SOMASE to do the count of how much each customer has already paid and compare it with the amount due

24. Hi Jose, do you have SKU values ​​repeated? This could cause an error. If it is not this, I can not know what may have happened just by your text.

25. Hello,

I tried to follow his example, but I think I did something wrong: in the Validos folder I put the SKUs of the products that are Active in column A and the word "active" in column B.
In the Conferencing folder, in column A has all SKU (both active and inactive). And in column B I put the following formula: = PROCV (\$ A1, VALID! \$ A \$ 1: \$ B \$ 223,2, FALSE)
Only the initial result (Check! B1) worked and in the following {B2 _ with = PROCV (\$ A2, VALID! \$ A \$ 1: \$ B \$ 223,2, FALSE}} appeared error # N / D

Do you know what might have happened? thankful

Joseph

26. Good morning,

My case works as follows.

I have client-side A and B column with values ​​on which it should.
In Column C the customer relationship that made payment so far, and column D the amount he paid. As there are many, I would need to make this comparison to know who is still a debtor and who has already paid with the amount that has already been paid. Is it possible in PROCV ?? Thank you.

27. Hi Cristiane, I do not understand how this structure works, but if it is for a single month, yes, the PROCV should solve your problem yes

28. Hi Adilson, maybe with VBA code you can, but I can not think of an Excel formula to help you with this

29. Hello, I need something I can not find anywhere.
In an accounting reconciliation column, I have hundreds of debit and credit amounts. I need to cancel credit and debit. Ex: 10 \$ a Debit and 10 \$ a Credit.
However, in this single column, you often have 10 \$ a Debit and 3 \$, 2 \$, 4 \$, 1 \$ a Credit, instead of the total amount (10 \$ in that case).
I wonder if there is a formula that would allow me to put the value I want, searching across the column, giving me only the cells that form the specific value in the counterpart.

30. Hello good afternoon! I hope you respond.

I am a nutritionist and I am trying to put together a spreadsheet with the data of patients I attend at a Hemodialysis clinic. What I need is to update their exams monthly. I have the worksheet with their name and the exams I want to use for the parameter and another worksheet with the updated exams. Is this update just by putting PROCV? help me

31. I believe this is changed in the step by step where cell C4 speaks, in fact it would be B4. But this very good tutorial, thank you!

32. Hi Maria, to get the information from the previous day is not very difficult, a macro copying and pasting solves, the problem is to update the changes, it would depend on where you pick them to create a code that only changed those different values

33. Good afternoon!
I need help, I use a patient sheet that changes daily, I enter the information there on that day, every day I have to see what has changed and update the new worksheet. They are over 900 patients and I spend almost two hours to upgrade! I need a way to update the new spreadsheet (which does not have the information from the previous day, but which may have changed data like the patient's name and bed - it's a hospital and may have new inmates or people gone) previous day (which has the new information). I've tried everything and still can not solve ... Can you help me with some idea? thanks!

34. Hi Alexander, just make a simple reference, assuming that the value is in the cell A1 of the Plan1 tab, in this case, just put in the other tab the formula = Plan1! A1

35. Good afternoon Rafael, how do I link information on a tab to another tab, for example. I want to inform a certain value in a tab and consequently this value will appear in the other linked tab.
Thank you.

36. It is possible to do otherwise, once you choose Europe, a list appears with the countries of Europe (this is done using INDIRECT within data validation). Now, if you want to choose France and appear written Europe, just create a table with these fields and use PROCV. If you want, you can have two independent lists, with continents and countries.

37. It depends on the organization of this table, but you can have all days of the year in columns and make a conditional leave, for each row, only the columns corresponding to the days of holidays with a different value. You can also use conditional formatting to change the color of these values

38. HAVE A SPREADSHEET WITH CODE AND DATE, EX: COLUMN A, CODES 1, 2, 3, 4 ... COLUMN B, 01 DATA / 01 / 18, 05 / 01 / 18, 20 / 01 / 18, 25 / 01 / 18, IN OTHER WORKSHEET WANT TO CREATE A fORMULA THAT WHEN THE COLUMN CONTAINS THE CODE EQUAL TO FIRST WORKSHEET, TELL THE dATE "HAVE OR OR OR WORKSHEET WITH OR CODE OR AND OR DATA, OR EX:. OR COLUMN OR OR OR CODES 1 OR 2 OR 3 OR 4 ... or column OR B OR DATA OR 01 / 01 / 18, OR 05 / 01 / 18, OR 20 / 01 / 18, OR 25 / 01 / 18, OR OR OTHERWISE WORKSHEET OR OR WANT TO CREATE OR OR OR OR OR FORMULA WHEN OR OR OR OR COLUMN CONTAINS OR OR CODE OR EQUAL OR OR OR OR FIRST WORKSHEET OR REPORT OR OR DATA. HAVE A SPREADSHEET WITH CODE AND DATE, EX: COLUMN A, CODES 1, 2, 3, 4 ... COLUMN B, 01 DATA / 01 / 18, 05 / 01 / 18, 20 / 01 / 18, 25 / 01 / 18, IN OTHER WORKSHEET I WANT TO CREATE A fORMULA THAT WHEN THE COLUMN CONTAINS THE CODE EQUAL TO FIRST WORKSHEET, TELL THE DATE. on any date

39. Hi Rafael, how are you?
Thank you for the help!
I have a question: The ProcV column will give me the information I need by choosing from an earlier column, which has a drop-down list.
Using your example, let's say the country column has a drop-down list, and once I choose France, I'd move on to "pull" the continent.
Is there any way for this to happen automatically, that is, once the country is chosen, the procv column is triggered automatically?
Thank you!

40. Good Morning! I have a holiday picture with dates and names. I wanted to know how I can superimpose on a table the dates that employees have vacations at the same time?

41. There are different ways to get at this result, a simple thing is to put the names directly on this third tab side by side and use conditional formatting for duplicate values

42. Hi Rodrigo, select all cells with names and use conditional formatting to check for duplicate values

43. Hi Adriano, I could not understand what you need, but in general, yes, it is possible to get information from one worksheet and use it in another

44. Good morning I'm trying to improve a spreadsheet in my work with the following intentions, my boss that I separate the weight shift "is a loading company" so I already have other worksheets that have weight and schedules will be if you have how to assemble another worksheet taking the information that is already filled and separate! 8: 00 to 16: 00 to 8: 00 to 18: 00 to 13: 00 to 23: 00 to XNUMX: XNUMX to XNUMX: XNUMX to XNUMX: XNUMX to XNUMX: XNUMX

45. Good afternoon, Rafael!

I have a situation, I have a spreadsheet in which I relate one side column B to several names and in column G another series of names, however I want to know a formula so I can check if any or some of the names in column B are in column G .
I await help

46. Rafael and when I have two tabs, both containing relation names, in a third tab I want to display the names that are repeated. It is possible to present the results of repeated names using PROCH

47. The lines need to be tied. For example, when you use a filter in a table, the values ​​do not mix. In this case, you need to change all at once or have formulas that automate this process when you dynamically change the values ​​in question

48. Good afternoon Rafael, blz?
I import a table with the column A-NAME that is ordered by column B-PRICE, whenever I update include inclusions of new NOMES and changes in the order.
After each update I copy the PRICES keeping a history in the columns C / D / E / F / G / H etc ... but as the table always changes order, my history data no longer coincides with columns A-NOMES and B -PREÇOS!
How do I solve this?
Thank you!

49. Hi Marcelo, how are you? Unfortunately we no longer have this spreadsheet, if you want, you will need to create it manually.

50. Good afternoon.
Please, can you download this worksheet to take the test?
Thank you.

51. Hi Leo, In Excel everything is a matter of table logic and organization. You can analyze previous or later columns, but for this you need to organize the formula so that Excel understands what you want.

52. GOOD AFTERNOON!
HAVE SOME WAY TO MAKE THESE DATA CRUZED INVERTED? INSTEAD OF PULLING THE COLUMN FROM THE FRONT, PULLING FROM THE PREVIOUS COLUMN?
THANKS

53. In such cases, a good chance is to create an extra tab and compile all the data there. Then just use a PROCV with the name and team time of the player in that big auxiliary table that will get it without problems

54. Hello Rafael, my name is Gerson and I loved your posts.
But I have a problem, in a spreadsheet, I have several tables each with a group and information example: TIME A is the name of one of the tables, and in it are the names of the team members and in the other columns are the size of the shirt of João and the size of his tennis, and so with Pedro and there goes ... in another table is the relationship of TIME B ...
I need in a report worksheet when I enter the TIME in one column and the NAME of the member in another, return me for example the size of this member's shirt ...
How to make? need help.