How to Create Table from Another Table in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


Create Table from Another Table in Excel: 3 Handy Ways

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.

Merging Multiple Columns to Create Table from Another Table in Excel

  • 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.

Formula Breakdown
  • 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)),””): The IFERROR 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 with Merged Cells in Excel


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.

Combining VLOOKUP and COLUMN Functions  to Create Table from Another Table in Excel

  • 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.

Formula Breakdown
  • 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.

Combining VLOOKUP and COLUMN Functions  to Create Table from Another Table in Excel

Read More: How to Create a Lookup Table in Excel


Similar Readings


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

Nesting INDEX and MATCH Functions to Create Table from Another Table in Excel

  • 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.

Formula Breakdown
  • 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.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it yourself.


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.


Related Articles

Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo