Fill table from other tables
I need excel formula to fill one table from 2 other tables in excel sheet.
Suppose we have two tables: table1(id,name), table2(id,date). I need to fill third table MyTable(id,name,date) from data in table1 and table2 based on matching IDs on these tables.
VLOOKUP doesn't help because this accepts one item as a searching key, I need function which accepts array of data as a searching key (list of IDs in my case).
Does someone know what formula to use?
Thanks!
If you have Excel 2007 or
If you have Excel 2007 or later, you can create Tables and then insert the following for Name cells in Table3:
=INDEX(Table1[Name],MATCH([ID],Table1[ID],0))
and similarly for Date cells:
=INDEX(Table2[Date],MATCH([ID],Table2[ID],0))
If you don't have Excel 2007 or later, then TableX[] & fieldnames must be replaced by the relevant ranges of the tables. (The MATCH function doesn't need Table3 to identify the ID field that is in Table3 but includes Table1/2 to indicate where it is looking outside its own domain.)
This copes with dates/names being present for ID's but not both and also tables that aren't sorted. A major limitation of this solution is it only does 1-1 matching (with the first occurence found of each duplicate entry); the query based method mentioned earlier is what's needed if you have (say) many dates to one ID/name combination.
HTH
I would try to create a query with two tables joined
Excel has built-in query tool called MS Query. Use it to choose two sheets as tables and join them by ID. MS Query will return result into your third sheet. If you need additional assistance, let me know.