Generate multiple lists of values that match criteria - Excel

Question

I'm trying to set up an Excel sheet but haven't been able to find example similar to my data setup and criteria. I've been trying to use index and small but am having trouble.

I'm trying to generate lists of values that match their own criteria.

The input data would appear like this:

  A       B            C            D
1 Name    Criteria1    Criteria2    Criteria3
2 name1   X                         W
3 name2   X            Y            X
4 name3                             A

I want to return lists of names where the criteria for them has any value so the result would look like this:

  A            B            C
1 Criteria1    Criteria2    Criteria3
2 name1        name2        name1 
3 name2                     name2
                            name3

Answer 1

I used a table and Structured References, but you could convert to relative or fixed addressing.

For the first Criteria first cell:

=IFERROR(INDEX(Table1[[Name]:[Name]],AGGREGATE(15,6,1/(LEN(Table1[Criteria1])>0)*ROW(Table1[Criteria1])-ROW(Table1[#Headers]),ROWS($1:1))),"")

Then fill down and across as needed

Using the AGGREGATE function enables us to ignore errors.

• 1/(len(xx)>0) will return a 1 or DIV/0 depending on presence of text in the cell.
• * Row(Table1) returns the row number or an error.
• Since AGGREGATE can ignore errors, only the valid row numbers will be returned.
• Use that return value to INDEX into the first column