Extrcating a list from another list, based on another cell.

[GazNicki]

I have a list of items in cells C7:C41 - this list contains duplicates.
In cells D7:C41 I have a either "yes" or "no".

In cells N7:N39, I wish to have a list of products from the list in C displayed only if the corresponding cell in D states Yes.

I have the following array formula in cell N7:N39 currently:

{=IFERROR(INDEX($C$7:$C$42,SMALL(IF($D$7:$D$42="Yes",ROW($C$7:$C$42)-MIN(ROW($C$7:$C$42))+1),ROW()-2)),"")}

This works, but I get duplicates.

Is there anyway of gathering this list without duplicates?

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

N7 =IFERROR(INDEX($C$7:$C$42,MATCH(0,IF($D$7:$D$42="yes",COUNTIF($N$6:N6,$C$7:$C$42)),0)),"")

 

[GazNicki]

This is perfect, thank you!

It even orders them in size order, from largest to smallest, which is awesome. Thank you!!

 

[admiral100]

You are welcome