Hi all,

I have a workbook, which have two sheets, Sheet A is like below:

aaa category1 111
aaa category2 222
bbb category1 333
bbb category2 444
bbb category3 555

and sheet B like below:

aaa
bbb

For which functions i should use to make sheet B like below - only get
category2 value.

aaa 222
bbb 444

Thanks a lot!

One way assuming the value to be returned is numeric.

On Sheet2:

A1 = aaa
B1 = category2

Enter this formula in C1 and copy down as needed:

=SUMPRODUCT(--(Sheet1!A$1:A$5=A1),--(Sheet1!B$1:B$5=B1),Sheet1!C$1:C$5)

--
Biff
Microsoft Excel MVP


"Coeus" wrote in message
...
Hi all,

I have a workbook, which have two sheets, Sheet A is like below:

aaa category1 111
aaa category2 222
bbb category1 333
bbb category2 444
bbb category3 555

and sheet B like below:

aaa
bbb

For which functions i should use to make sheet B like below - only get
category2 value.

aaa 222
bbb 444

Thanks a lot!

--Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=INDEX(SheetA!$C$2:$C$100,MATCH(1,(SheetA!$A$2:$A$ 100=A2)*
(SheetA!$B$2:$B$100="category2"),0))


--If you have numerics in SheetA colC the below will return the sum of
category2 and matching criteria A2. If the data in SheetA is aunique list you
may use this instead of the above array formula..

=SUMPRODUCT(--(SheetA!A2:A100=A2),--(SheetA!B2:B100="category2")
,SheetA!C2:C100)


If this post helps click Yes
---------------
Jacob Skaria


"Coeus" wrote:

Hi all,

I have a workbook, which have two sheets, Sheet A is like below:

aaa category1 111
aaa category2 222
bbb category1 333
bbb category2 444
bbb category3 555

and sheet B like below:

aaa
bbb

For which functions i should use to make sheet B like below - only get
category2 value.

aaa 222
bbb 444

Thanks a lot!