Tanjim Reza
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.
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.
- 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.
- 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.
Consequently, you will be able to create a table from another table according to your desired criteria.
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.
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.
- 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.
- 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.
As a result, you will get the updated Table1 from another table with Employee Name criteria. And, the final result should look like this.
Read More: How to Create a Lookup Table 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.
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.
- 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.
- Now, place your cursor in the bottom right position of the cell and drag the fill handle downward subsequently.
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.
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.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
Related Articles
- Create Table in Excel Using Shortcut
- Create a Table in Excel Based on Cell Value
- How to Create a Table with Existing Data in Excel
- How to Create a Table Without Data in Excel
- How to Create a Table with Merged Cells in Excel
- How to Create a Table in Excel with Multiple Columns
- How to Make a Table in Excel with Lines
- How to Create a Table with Subcategories in Excel
- How to Make 3D Table in Excel
- How to Make a Conversion Table in Excel
- How to Make a Decision Table in Excel
- How to Create a League Table in Excel
- How to Mirror Table on Another Sheet in Excel
- How to Create Table from Multiple Sheets in Excel
- How to Make a Table Bigger in Excel
- How to Create a Table with Headers Using Excel VBA
- How to Add New Row Automatically in an Excel Table
