Help! Need to create some sort of vertical list of text based certain criteria

[benwahchang]

See attached sheet. I am looking to automatically have a list populate under 'Adam's Committed Direct Reports' with the names of such. So it should be Bob, Mark, Robert, Kristen and Kelly. Is there a way to get it to update when those names change. The box I have where I need to list would be on a different tab in my actual worksheet.

[XOR LX]

Hi,

In cell H2 enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) and copy down until you start getting blanks:

=IFERROR(INDEX($B$1:$B$17,SMALL(IF((($A$1:$A$17="Adam")*($C$1:$C$17="Committed"))>0,ROW($A$1:$A$17)),ROWS($A$1:$A1))),"")

Regards

[benwahchang]

Wicked! Are you able to explain how that works? I'm always trying to learn/understand from these types of answers. Is that the easiest way to do this as well. How about if wanted to only have one column and then input a different leaders name?

[XOR LX]

Sure.

The first part of: IF((($A$1:$A$17="Adam")*($C$1:$C$17="Committed"))>0 checks to see which of the entries in A1:A17 are "Adam" and the second part checks to see which of the entries in C1:C17 are "Committed". For each of these comparisons, Excel returns an array containing TRUE or FALSE, e.g.

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

It then multiplies these two arrays, which, since in Boolean logic the only result which will return TRUE from this array of products is TRUE*TRUE, effectively acts as an AND operator (i.e. the cell in the A column is "Adam" AND the cell in the corresponding row in the C column is "Committed").

Excel also usefully converts TRUE to 1 and FALSE to 0 (though this must be coerced in some cases) so that, another way of looking at the above is that only the results of TRUE*TRUE, i.e. 1*1=1 will meet our ">0" criterion (FALSE*TRUE=0*1=0).

The resulting array of TRUE/FALSE is then used in the IF statement to produce a further array of ROW numbers, where TRUE, and FALSE where FALSE, e.g.:

{FALSE;2;FALSE;4;5;FALSE;7;8;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The SMALL function then takes the kth smallest of these, depending on the second clause in the function (ROWS($A$1:$A1)), which, since this latter counts the number of rows between two cell references, and since, when copied down into further rows becomes successively (due to its relative row referencing) ROWS(($A$1:$A2),ROWS(($A$1:$A3),ROWS(($A$1:$A4),..., i.e, 1,2,3,4,...
it will return the smallest, second smallest, third smallest, etc. etc. on successive lines.

Finally, the row number is used in the INDEX to return the corresponding entry in the 'Leader' column.

Regards