Merge two tables using formulas only

Copper Contributor

Hi,

I've been doing some Excel exercises recently, but I'm stuck on this one. 

Is it possible to fill in the table in Sheet1 with information from Sheet2, but without using power query, just formulas? Also, data, that is already there in Sheet1 cannot be moved. How can I do this?

10 Replies

@GrazieRagazzo 

 

Since you have not specified what you are supposed to get in the Table,, therefore I've used Name to pull records from one to another Table.

 

Read the instruction and follow, hope this help You !!

@GrazieRagazzo With formulae only!! Then you need a third table, as demonstrated in the attached file.

Screenshot 2021-03-20 at 07.03.40.png

@GrazieRagazzo 

Please try this...

 

On Sheet1

In B2

=IFERROR(INDEX(Table2[owner],MATCH(Table1[@[Nr]:[Nr]],Table2[[Nr]:[Nr]],0)),"")

and then copy it across and down.

 

Please refer to the attached for more details...

 

 

@Rajesh_Sinha I have a similar issue, but with an added wrinkle or two. The examples posted seem like they might do the job, but I want to merge info from 2 tables that have different columns, and which also may have repeated names in one column in one table, but not the other.

 

The only shared column will be Name. And in the main table, there will be no double ups, as it's a running list of "clients." The second table is a list of interactions with clients, so they may appear more than once. The second list has no reference to their basic info. Age/DOB, and other things which don't change, I'd like to be able to add that. 

 

I'll attach a very simplified example spreadsheet to give the idea. Any help appreciated.

 

 

@Davidm54 Similar to the solution suggested by @Subodh_Tiwari_sktneer earlier, but adopted to your specific requirements. See attached.

 

@Davidm54 

 

Check the attached Sheet, I've solved using Yellow Cells headers,,, read the instruction,, confirm whether is working or not,, in case of queries please share expected output with me!

Thanks for this. It's been decided by higher ups that the other list can be static now, but I have another sheet this may be useful for. Will definitely check it.
Again, Thanks for this. It's been decided by higher ups that the other list can be static now, but I have another sheet this may be useful for. Will definitely check it.
Thanks to you both, checked this again, and they have sorted problem, and will help with another sheet as well. Also, I note in yours, Riny, that [column]:[column] seems to do the work with extending things as they're added, that's very good to know.
I'm having a similar issue, and you've almost gotten to the solution. I'm currently trying to Merge Tables where
Table 1 has a column of IDs, and other columns of data that is irrelevant.
Table 2 has some of the same IDs (no additional IDs to Table 1) and these IDS sometimes are repeated with different information.
This is similar to "Sam" having multiple order of different items.
I would like a table that would combine all of the "Sam" rows into 1 row, where if there is multiple data it would be deliminated, i.e., by a comma.

I've been trying to use the Power Query Editor, but I keep getting an Expression.Error: Evaluation ran out of memory and can't continue. This is very frustrating since I was just upgraded to our most powerful/highest memory Engineering level laptop to be able to perform these type of functions.