Bishawajit Chakraborty
Sometimes when dealing with Excel tables, we need to create a new table depending on requirements from the existing data. In this article, I’ll demonstrate 3 effective techniques to create a table from another table in Excel that fulfills specific requirements.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
3 Handy Ways to Create Table from Another Table in Excel
This article will demonstrate to you how to create a table from another table in Excel by merging multiple functions, combining VLOOKUP and COLUMN functions, and nesting INDEX and MATCH functions.
1. Merging Multiple Columns to Create Table from Another Table in Excel
You need to merge the IFERROR, INDEX, SMALL, IF, and ROW functions to build a new table called new table from this one, where there will be two columns labeled Male and Female and you will see the employee name at last.
Steps:
- So, you can see the given data set in the below image.
- Firstly, choose the F6 cell.
- Secondly, write down the following formula.
=IFERROR(INDEX($C$6:$C$15,SMALL(IF($D$6:$D$15=F$5,(ROW($D$6:$D$15)-5),FALSE),ROW()-5)),"")- Thirdly, press Enter.
- ROW($D$6:$D$15)-5),FALSE),ROW()-5): The ROW function returns the row number for reference.
- IF($D$6:$D$15=F$5,(ROW($D$6:$D$15)-5),FALSE): This section returns the value after checking the condition whether it is true or false.
- SMALL(IF($D$6:$D$15=F$5,(ROW($D$6:$D$15)-5),FALSE),ROW()-5)),””): The SMALL function outputs a numeric number based on its position inside a list when the values are sorted in ascending order.
- INDEX($C$6:$C$15,SMALL(IF($D$6:$D$15=F$5,(ROW($D$6:$D$15)-5),FALSE),ROW()-5)),””): The INDEX function extracts a value from a table or range, or a reference to a value, and returns it.
- =IFERROR(INDEX($C$6:$C$15,SMALL(IF($D$6:$D$15=F$5,(ROW($D$6:$D$15)-5),FALSE),ROW()-5)),””): This function represents the correct result if there is any error.
- After that, you will see the first employee’s name here.
- Then use the Fill Handle tool and drag it rightward and downward to see all the employee name.
- Finally, you will get all the results here.
Read More: How to Create a Table in Excel with Multiple Columns
2. Combining VLOOKUP and COLUMN Functions to Create Table from Another Table in Excel
In this section, we will discuss another way to create a table from another table in Excel by combining the VLOOKUP and the COLUMN functions. So, to know this method, follow the below steps accordingly.
Steps:
- Let’s say you have two tables and a dataset of employees. Employee Name and ID are the two columns in Sample Table 1. Additionally, Sample Table 2 has two columns with the names Joining Date and ID. You must now insert the joining dates column from Sample Table 2 into your New Table.
- Firstly, select the D19 cell.
- Then, write down the following formula.
=IFERROR(VLOOKUP($B19,$E$6:$F$15,COLUMN(B18),FALSE),"")- After that, hit Enter.
- COLUMN(B18): The COLUMN function returns the particular column number.
- VLOOKUP($B19,$E$6:$F$15,COLUMN(B18): This portion shows a certain value for a specified range.
- =IFERROR(VLOOKUP($B19,$E$6:$F$15,COLUMN(B18),FALSE),””): The IFERROR function represents the correct result if there is any error.
- Therefore, you will see the first employee’s joining date here.
- Besides, use the Fill Handle tool and drag it down from the D19 cell to the D28 cell.
- As a result, you will get all the employees joining dates in the below image.
Read More: How to Create a Lookup Table in Excel (5 Easy Ways)
Similar Readings
- How to Create a Table with Merged Cells in Excel (4 Easy Methods)
- Create a Table Without Data in Excel (2 Easy Methods)
- How to Make a Table in Excel with Lines (with Easy Steps)
- Excel VBA to Create Table from Range (6 Examples)
- How to Create Excel Table with Row and Column Headers
3. Nesting INDEX and MATCH Functions to Create Table from Another Table in Excel
In this last section, we will demonstrate another method to create a table from another table in Excel by nesting the INDEX and MATCH functions. So, to know this method, follow the below steps accordingly.
Steps:
- Let’s suppose you have two tables, Sample Table 1 and Sample Table 2, respectively. ID and Employee Name are two of the columns in the first table. Additionally, the other table has fields for Expenses and Employee Name. You must now enter the employee IDs in Sample Table 2 in accordance with Sample Table 1’s
- Firstly, choose the C19 cell.
- Then, write down the following formula.
=INDEX(Sample_Table1,MATCH(B19,Sample_Table1[Employee Name],0),1)
- After that, hit Enter.
- MATCH(B19,Sample_Table1[Employee Name],0): The MATCH function looks for a specific item within a set of cells, finds it, and then returns the item’s position within the set of cells.
- =INDEX(Sample_Table1,MATCH(B19,Sample_Table1[Employee Name],0),1): This INDEX function extracts a value from a table or range, or a reference to a value, and returns it.
- Consequently, you will get the first employee’s ID in the below image.
- Besides, use the Fill Handle tool and drag it down from the C19 cell to the C28 cell.
- Lastly, you will find here all employee’s IDs in the given image.
Read More: How to Create Table from Another Table with Criteria in Excel
Conclusion
In this article, we’ve covered 3 handy ways to create a table from another table in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
