How to Create Table from Another Table with Criteria in Excel

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.


Download Practice Workbook

You can download our practice workbook from here for free!


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

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 a Table with Existing Data 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 Create a Table in Excel with Multiple Columns


Similar Readings


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: Create a Table in Excel Based on Cell Value (4 Easy Methods)


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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo