Hi,

Try this

=SUMPRODUCT((Sheet1!$A$1:$C$1=Sheet2!$A1)*(Sheet1! $A$2:$C$4))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Maulwy" wrote in message
...
If I had data:
Sheet1:
A B C
1 AAA BBB CCC
2 1 4 7
3 2 5 8
4 3 6 9

Sheet2:
A B
1 CCC 24
2 BBB 15
3 AAA 6


Where B2 is the sum of Sheet1: C2-C4, after HLOOKUP function in cell A1,
as
well as B3 and B4 cells, do HLOOKUP sum based on the results.
Is there a formula that can meet these conditions?
Thank you for the information.

Regards,
Maulwy