Array Formula

[sachar]

Dear all,

With reference to the attachment, can be found the “ output” result in another simple way without using the Array Formula?

 

[Khalid NGO]

Hi Sachar,
This is not very complicated array, so you can live with it.
I am not sure it can be done without using array.

But pivot is an option for you which is much faster.

Select your range A1:B6
Insert > Pivot

Now use Type as Report Filter
and Name as Row Labels

You can now easily filter your result the way you want.

See the attached.

Regards,

 

[Hui]

If your worried about the error messages simply add and Iferror() function around it

=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)),ROW(A1))),"") Ctrl+Shift+Enter

 

[sachar]

Dear,

No I am not worried about the error but, i could not understood this formula, Can you'r explore me in detail the formula.

Thanking you,

 

[Khalid NGO]

Dear,

No I am not worried about the error but, i could not understood this formula, Can you'r explore me in detail the formula.

Thanking you,

here is the link
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[coolsac12]

If your worried about the error messages simply add and Iferror() function around it

=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)),ROW(A1))),"") Ctrl+Shift+Enter

Hi @Hui ,
Is there any way that I would ignore the multiple values in the same query.
Like: Apple exists in "B1" and "B4" but I want to only 1 Apple as result, even results are multiple for the same.

Thanks in advance

 

[Deepak]

Another non array approach!!


=INDEX($B$1:$B$6,INDEX(SMALL(($A$2:$A$6=$D$2)*ROW($A$2:$A$6),COUNTIF($A$2:$A$6,"<>"&$D$2)+ROW(A2)-1),))

=INDEX($B$1:$B$6,SUMPRODUCT(SMALL(($A$2:$A$6=$D$2)*ROW($A$2:$A$6),COUNTIF($A$2:$A$6,"<>"&$D$2)+ROW(A2)-1)))