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)

Insurance​	Insurance Policy​	Product Fabric​	Total Premium​	Net premium​	Tax Quantity 1​	Commission amount​	Commission amount - Validation​
21​	xxxxxx​	FAMPD	3227​	1746​	0​	1481​	0​
21​	yyyyy​	FAMPC	1849​	1109​	0​	740​	0​

<tbody> </tbody>

PRODUCT CODE	Commission
xxxxxx	40.00%
xxxxxx	40.00%
xxxxxx	40.00%
xxxxxx	40.00%
xxxxxx	40.00%
yyyyy	45.88%
yyyyy	45.88%
yyyyy	45.88%
yyyyy	45.88%
yyyyy	45.88%

<tbody> </tbody>

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.