Data Extraction from Worksheet1 (ws1) for Worksheet2 (ws2)

[nishant859@yahoo.co.in]

My Query is:-

I have Raw Data in ws1, now I want to extract some required data without using pivot table & VBA in ws2

Data in ws1

Brand (InCell A1) Product (In Cell B1)

a 1

b 2

c 3

a 4

b 5

Now in ws2, I want to know the name of product (in different cells) we under Brand Name

a

b

 

[bobhc]

Good day nishant859@yahoo.co.in


I think you may wish to shorten your ID name as it runs in to the question box and members will find it some what irritating.

 

[Lymm]

Have you tried Vlookup?

 

[nishant859@yahoo.co.in]

But Vlookup returns only the first value from ws1, like for Brand A,vlookup return product 1, but i want both 1 & 4

&& how could i shorten my name

 

[Kevin@Radstock]

Hi


Assuming your data is in A1:B6 including your column headers. In D1 = a & E1 = b.

Then in A2:

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=D$1,ROW($A$1:$A$6)-ROW($A$1)+1,""),ROW(A1))),"") This is an array formula: CTRL + SHIFT + ENTER


Copy across to B2 and down.