Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Nov 2017
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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?
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular
    Join Date
    Nov 2017
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Sum / Lookup formula -Query

    You could use INDEX MATCH in that case then, and multiply by the total premium figure.
    √-1 2³ ∑ π
    …And it was delicious!

  5. #5
    Board Regular
    Join Date
    Nov 2017
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •