How to Create a Table from Another Table with Criteria in Excel (3 Methods)

Method 1 – Creating a Table Based on Criteria Using INDEX and ROW Functions

We’ll use 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

We need to create another table named Created_Table from the table above, where there will be two columns named Male and Female and the employee names will be inserted accordingly.

We can do this by merging the IFERROR, INDEX, SMALL, IF and ROW functions.

Steps:

  • Begin by clicking on cell F5 and create a new table named Created_Table with the necessary column headings.

Created Table

  • Next, click on cell F6 and enter 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)),"")
    • This formula retrieves the names of male employees based on the dataset.
  • Press Enter to get the name of the first male employee.

Formula to Create Table from Another Table with Criteria in Excel

  • Drag the fill handle rightward and downward to complete the table.
    • The subtraction of 5 from the ROW function accounts for the formula being in the 6th row and looking up criteria from the 6th row. Adjust this value as needed.

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

  • We’ll now have a table created from another table based on our desired criteria.

Created Table from Another Table with Criteria in Excel

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


Method 2 – Updating Table1 Using VLOOKUP and COLUMN Functions

We’ll use 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, we need to update Table1 by inserting the joining dates column from Table2.

We can use the VLOOKUP, IFERROR and COLUMN functions together to do this.

Steps:

  • Create a new table named Updated_Table1 with an additional column for Joining Date.

Updated Table 1

  • Click on cell D19 and enter the following formula:
=IFERROR(VLOOKUP($B19,$E$6:$F$15,COLUMN(B18),FALSE),"")
    • This formula retrieves the joining date for Sarah from Table2.
  • PressEnter.

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

  • Use the fill handle to copy the formula for other employees.

Drag Fill Handle Below

The updated Table1 will now include joining dates based on Employee Name criteria.

Updated Table 1 from Another Table with Criteria in Excel

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


Method 3 – Updating a Table Using INDEX and MATCH Functions

We can use INDEX and MATCH functions to update a table from another table.

If we are given two tables named Sample_Table 1 and Sample_Table2 and in the first table, there are two columns named Employee ID and Employee Name.  The other table contains Employee Name and Sales columns.

We need to insert employee ids in Sample_table2 according to Sample_Table1.

We can achieve this by using the INDEX and MATCH functions.

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

  • Create a new table named Updated_Table2 similar to Sample_Table2 but with an extra column for Employee ID.

Created the Updated Table 2

  • Click on cell C19 and enter the following formula:
=INDEX(Sample_Table1,MATCH(B19,Sample_Table1[Employee Name],0),1)
    • This formula retrieves the Employee IDs based on their names from Sample_Table1.
  • Press Enter.

Formula to Update Table 2 with Criteria in Excel

  • Drag the fill handle downward to complete the Employee IDs for all names.

Drag Fill Handle Below

Our updated table will now successfully incorporate Employee IDs based on criteria. The outcome should resemble the provided example.

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 the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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