Extracting a unique list of values based on set criteria

[LearnExcl]

Hello All,

Column A houses alphanumeric values. Column C houses 0s or 1s. Column E houses text. What I would like in Column H is a list of unique values extracted from Column A where values in Column C is 1. In Column I, I need to extract a unique list of values from A where cells in Column C are 1s AND cells in Column E are nonblank.

Your assistance will be greatly appreciated.

 

[steve the fish]

This way requires that row 1 has headers or is left blank. So in H2:

=IFERROR(INDEX($A$2:$A$10,MATCH(0,IF($C$2:$C$10=1,COUNTIF($H$1:H1,$A$2:$A$10)),0)),"")

and I2:

=IFERROR(INDEX($A$2:$A$10,MATCH(0,IF($C$2:$C$10=1,IF($E$2:$E$10<>"",COUNTIF($I$1:I1,$A$2:$A$10))),0)),"")

Both are array formulas and require entering with CTRL-SHIFT-ENTER. You will need to adjust the ranges no doubt.

 

[LearnExcl]

Thank you very much, Steve. I just had to add the "match type" (0) to the formula and it started working just fine. One thing I would like to ask though, could you please explain the Match() portion of the formula? I really want to get educated on that piece as I am having difficulty understanding it. Specifically, the countif portion in which the criteria is a whole range ($A$2:$A$10) being compared against $I$1:I1. Any and all help will be appreciated.

 

[steve the fish]

That produces an array of COUNTIF results. The MATCH looks for the first zero in the array that satifies the condition/s. We want the zero because that means it doesnt appear in our list already so it produces a unique list. The evaluate formula tool works very well to explain what is going on.