Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup for multiple lists (2003)

    Attached is a sample of what I am trying to do. The user will select a company and then policy type from a list. There are 3 companies and 9 policy types. Each company has the exact same 9 policy types, but the payout percentages vary by company/policy type combination. Meaning that Company 'A' Policy 'Type 1' may have a payout percentage that is different from Company 'B' Policy 'Type 1' even though the policy types are both 'Type 1'.

    The Company and Policy Type need to remain separate categories.

    Any thoughts on how to make the payout percentage in column E calculate automatically based on the selection combination in columns C and D?

    Any help would be much appreciated!! Thanks!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup for multiple lists (2003)

    You could place this formula in E2, format as a percentage, then fill down:

    =SUMPRODUCT(($L$3:$L$29=C2)*($M$3:$M$29=D2)*$N$3:$ N$29)

    Or this array formula (confirm with Ctrl+Shift+Enter):

    =INDEX($N$3:$N$29,MATCH(C2&D2,$L$3:$L$29&$M$3:$M$2 9))

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup for multiple lists (2003)

    Thanks Hans!!

    Worked perfectly!

Posting Permissions

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