How to Create Table from Another Table with Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

When working with Excel tables, sometimes we need to create another table from the existing tables based on criteria. In this article, I will show you 3 suitable ways to create a table from another table with criteria in Excel.


Create Table from Another Table in Excel with Criteria: 3 Suitable Ways

1. Merge INDEX and ROW Functions to Create Table from Another Table Based on Criteria

Say, you are given a table named Source_table with three columns named Employee ID, Employee Name, and Gender.

Source Table to Create Table from Another Table with Criteria in Excel

Now, you need to create another table named Created_Table from this table, where there will be two columns named Male and Female and the employee names will be inserted accordingly. You can do this by merging the IFERROR, INDEX, SMALL, IF and ROW functions.

Follow the steps below to do this.

📌 Steps:

  • First and foremost, click on cell F5 and create your table named Created_table with the necessary headings.

Created Table

  • Afterward, click on cell F6 and insert 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)),"")
  • Subsequently, hit the Enter key.

Formula to Create Table from Another Table with Criteria in Excel

  • Following, you will get the name of the first male employee according to your dataset.
  • Now, place your cursor in the bottom right position of the cell.
  • Subsequently, a black fill handle will appear.
  • Following, drag the fill handle rightward and downward to complete your table.

Drag Fill Handle to Create Table from Another Table with Criteria in Excel

Consequently, you will be able to create a table from another table according to your desired criteria.

Created Table from Another Table with Criteria in Excel

Note:

Here, we have subtracted 5 from the ROW function result. It is because we are putting the formula in the 6th row and also looking up the criteria in the array from the 6th row. If looking up criteria row changes or formula cell changes, the subtraction value can also change. We always will have to subtract a number from the ROW function to make the subtraction result 1.

Read More: How to Create Table from Another Table in Excel


2. Using VLOOKUP and COLUMN Functions

Say, you have a dataset of employees with two tables. Table1 has 2 columns: Employee ID and Employee Name. And Table2 contains two columns named Employee ID and Joining Date.

2 Given Tables to Create Table from Another Table with Criteria in Excel

Now, you need to update Table1 by inserting the joining dates column from Table2. You can use the VLOOKUP, IFERROR and COLUMN functions together to do this.

Go through the steps below to accomplish the result.

📌 Steps:

  • At the very beginning, create a new table named Updated_Table1 with an extra column named Joining Date.

Updated Table 1

  • Afterward, click on cell D19 and insert the following formula.
=IFERROR(VLOOKUP($B19,$E$6:$F$15,COLUMN(B18),FALSE),"")
  • Subsequently, press the Enter key.

Formula to Update Table 1 from Another Table with Criteria in Excel

  • As a result, you will get the joining date for Sarah from Table2.
  • Now, for the other employees, use the fill handle feature below to copy the formula.

Drag Fill Handle Below

As a result, you will get the updated Table1 from another table with Employee Name criteria. And, the final result should look like this.

Updated Table 1 from Another Table with Criteria in Excel

Read More: How to Mirror Table on Another Sheet in Excel 


3. Combining INDEX and MATCH Functions

Moreover, you can use INDEX and MATCH functions to update a table from another table. Say, you are given two tables named Sample_Table 1 and Sample_Table2. In the first table, there are two columns named Employee ID and Employee Name. And the other table contains Employee Name and Sales columns. Now, you need to insert employee ids in Sample_table2 according to Sample_Table1.

Sample Tables to Create a Table from Another Table with Criteria in Excel

You can achieve this target by using the INDEX and MATCH functions. Follow the steps below to achieve this target.

📌 Steps:

  • First, create a new table named Updated_Table2 like the Sample_Table2 table, but with an extra column named Employee ID.

Created the Updated Table 2

  • At this time, click on cell C19 and insert the following formula.
=INDEX(Sample_Table1,MATCH(B19,Sample_Table1[Employee Name],0),1)
  • Subsequently, hit the Enter key.

Formula to Update Table 2 with Criteria in Excel

  • Now, place your cursor in the bottom right position of the cell and drag the fill handle downward subsequently.

Drag Fill Handle Below

Thus, you will get the Employee IDs according to their names and you will be able to update a table successfully from a table with criteria. And, the outcome should look like this.

Updated Table 2 from Another Table with Criteria in Excel

Read More: How to Create Table from Multiple Sheets in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, in this article, I have shown you 3 suitable ways to create a table from another table with criteria in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.


Related Articles


<< Go Back to Make a Table | Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo