Sum / Lookup formula -Query

[JoeRooney]

Hi,

I have the below tables and I am trying to match back avalue from a separate product table & then output a calculation dependingon the value found.

I have the below formula which looks fine to me but I cantfigure out why it only returns a 0 value.

The formula should find Product Fabric under Product Code inthe Product table & then Multiply the value under total premium * thecommission % in the product table.

=SUM(LOOKUP(C:C,'Product Table'!A:A,D:D)*Product Table!B:B)

[TABLE="width: 398"]
<tbody>[TR]
[TD="width: 64"]

Insurance​

[/TD]
[TD="width: 64"]

Insurance Policy​

[/TD]
[TD="width: 64"]

Product Fabric​

[/TD]
[TD="width: 64"]

Total Premium​

[/TD]
[TD="width: 64"]

Net premium​

[/TD]
[TD="width: 64"]

Tax Quantity 1​

[/TD]
[TD="width: 64"]

Commission amount​

[/TD]
[TD="width: 83"]

Commission amount - Validation​

[/TD]
[/TR]
[TR]
[TD="width: 64"]

21​

[/TD]
[TD="width: 64"]

xxxxxx​

[/TD]
[TD="width: 64"] FAMPD
[/TD]
[TD="width: 64"]

3227​

[/TD]
[TD="width: 64"]

1746​

[/TD]
[TD="width: 64"]

0​

[/TD]
[TD="width: 64"]

1481​

[/TD]
[TD="width: 83"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 64"]

21​

[/TD]
[TD="width: 64"]

yyyyy​

[/TD]
[TD="width: 64"] FAMPC
[/TD]
[TD="width: 64"]

1849​

[/TD]
[TD="width: 64"]

1109​

[/TD]
[TD="width: 64"]

0​

[/TD]
[TD="width: 64"]

740​

[/TD]
[TD="width: 83"]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 151"]
<tbody>[TR]
[TD="width: 115"] PRODUCT CODE
[/TD]
[TD="width: 87"] Commission
[/TD]
[/TR]
[TR]
[TD="width: 115"] xxxxxx
[/TD]
[TD="width: 87"] 40.00%
[/TD]
[/TR]
[TR]
[TD="width: 115"] xxxxxx
[/TD]
[TD="width: 87"] 40.00%
[/TD]
[/TR]
[TR]
[TD="width: 115"] xxxxxx
[/TD]
[TD="width: 87"] 40.00%
[/TD]
[/TR]
[TR]
[TD="width: 115"] xxxxxx
[/TD]
[TD="width: 87"] 40.00%
[/TD]
[/TR]
[TR]
[TD="width: 115"] xxxxxx
[/TD]
[TD="width: 87"] 40.00%
[/TD]
[/TR]
[TR]
[TD="width: 115"] yyyyy
[/TD]
[TD="width: 87"] 45.88%
[/TD]
[/TR]
[TR]
[TD="width: 115"] yyyyy
[/TD]
[TD="width: 87"] 45.88%
[/TD]
[/TR]
[TR]
[TD="width: 115"] yyyyy
[/TD]
[TD="width: 87"] 45.88%
[/TD]
[/TR]
[TR]
[TD="width: 115"] yyyyy
[/TD]
[TD="width: 87"] 45.88%
[/TD]
[/TR]
[TR]
[TD="width: 115"] yyyyy
[/TD]
[TD="width: 87"] 45.88%
[/TD]
[/TR]
</tbody>[/TABLE]

I have tested this formula with simplified tables and itworks fine , I just cant figure out why it is not working on the above tables.

Any help is greatly appreciated.

Thanks

 

[tyija1995]

Hey,

Are you looking for a formula to return a value for the "Commission amount - Validation" column? I assume it is supposed to equal the "Commission amount" as well?

 

[JoeRooney]

Hi,

Yes I want to return the commission amount & then multiply this amount by the figure in Total premium.

 

[tyija1995]

You could use INDEX MATCH in that case then, and multiply by the total premium figure.

 

[JoeRooney]

Hi ,

Thanks for the suggestion , i have tried using =INDEX(ProductTable!B:B,MATCH(C:C,ProductTable!A:A,0),1)

But I still only receive a zero value.