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.

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.

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.

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

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.

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.

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.
Press Enter.

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

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

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.