# Thread: Sum / Lookup formula -Query Thanks: 0 Likes: 0

1. ## Sum / Lookup formula -Query

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

 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%

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

2. ## Re: Sum / Lookup formula -Query

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?

3. ## Re: Sum / Lookup formula -Query

Hi,

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

4. ## Re: Sum / Lookup formula -Query

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

5. ## Re: Sum / Lookup formula -Query

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.