Auto-populate one table from another table if a criteria is met

I have two tables: Information and List.

The Information table has two columns: Name and Gender.

The List table also has two columns: Male and Female.

I'm trying to auto-populate the List table with the names of the people who are either male or female, by using formulas.

I tried to use INDEX() and MATCH()

=INDEX(A:A, MATCH(D1, B:B, 0),1)

but it only returns the first name with the corresponding gender.

Any ideas?

1 Answer

Since the matched data skips rows, you cannot use a simple index/match formula to extract the names (without using helper columns, that is).

The simplest solution requires the use of an array formula:

Array enter (Ctrl+Shift+Enter) the following formula in D2 and copy-paste/fill-down/fill-right into the rest of the table columns D and E (don't forget to remove the { and }):

{=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE),ROW()-1)),"")}

The formula works by initially constructing an array that contains the row index if the gender matches, or FALSE otherwise: IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE).

Then the SMALL() function is used to extract the next smallest index corresponding to the data row of the List table: SMALL({…},ROW()-1). The reason this works is that the SMALL() function ignores boolean values.

Note that, if entering the formula in any row other than row 2, or if after entering the formula rows are inserted/deleted above the first List table data row, the ROW()-1 part needs to be adjusted so that the result is 1 for the first data row.

Finally, this index is used to extract the appropriate name: INDEX($A:$A,<next smallest index>).

The IFERROR() is just there to hide the #NUM! errors that occur when the SMALL() function runs out of valid indexes to return.

A more robust, but more complicated, version of the formula that automatically adjusts for the number of data rows in the Information table, and won't break if rows are inserted/deleted above the first data row of the List table, follows:

{=IFERROR(INDEX($A:$A,SMALL(IF($B$1:INDEX($B:$B,COUNTA($B:$B))=D$1,ROW($B$1:INDEX($B:$B,COUNTA($B:$B))),FALSE),ROW()-ROW($B$2)+1)),"")}