Dear Sir,

I have two tables or blocks.

Price Nos
a b c d d e f a c b
5 6 7 8 6 2 1 6 3 4
2 5 4 9 7 8 9 5 2 3

a 40
b 39
c 29
d 111

I want a formula which will search for "a" in block "Price and also in block
"Nos" and then sumproduct the said column and give result such as "a" - 40,
"b"-39 etc.

Nimish

I put your first block in A1:D3 and the second block in G1:L3, and
then put a b c d into A7:A10 and this formula in B7:

=INDEX(A$2:D$2,MATCH(A7,$A$1:$D$1,0))*INDEX(G$2:L$ 2,MATCH(A7,$G$1:$L
$1,0))+INDEX(A$3:D$3,MATCH(A7,$A$1:$D$1,0))*INDEX( G$3:L$3,MATCH(A7,$G
$1:$L$1,0))

Copy the formula into B8:B10 to get the numbers in your summary table.

Hope this helps.

Pete

On Jan 24, 10:30*am, Nimish Shah
wrote:
Dear Sir,

I have two tables or blocks.

Price * * * * * * * * * * * * * * * * * * *Nos
a *b * c *d * * * * * * * * * * * * d *e *f *a *c *b
5 *6 * 7 *8 * * * * * * * * * * * * 6 *2 *1 6 *3 *4
2 *5 * 4 *9 * * * * * * * * * * * * 7 *8 *9 *5 *2 *3

a 40
b *39
c *29
d 111

I want a formula which will search for "a" in block "Price and also in block
"Nos" and then sumproduct the said column and give result such as "a" - 40,
"b"-39 etc.

Nimish *

Please don't multipost.
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html
--
David Biddulph

"Nimish Shah" wrote in message
...
Dear Sir,

I have two tables or blocks.

Price Nos
a b c d d e f a c b
5 6 7 8 6 2 1 6 3 4
2 5 4 9 7 8 9 5 2 3

a 40
b 39
c 29
d 111

I want a formula which will search for "a" in block "Price and also in
block
"Nos" and then sumproduct the said column and give result such as "a" -
40,
"b"-39 etc.

Nimish

See your other post

http://tinyurl.com/2mcuwh

--
Biff
Microsoft Excel MVP


"Nimish Shah" wrote in message
...
Dear Sir,

I have two tables or blocks.

Price Nos
a b c d d e f a c b
5 6 7 8 6 2 1 6 3 4
2 5 4 9 7 8 9 5 2 3

a 40
b 39
c 29
d 111

I want a formula which will search for "a" in block "Price and also in
block
"Nos" and then sumproduct the said column and give result such as "a" -
40,
"b"-39 etc.

Nimish