Thread: Lookup for multiple lists (2003)

1. 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!

2. 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. 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
•