Dynamic Lookups Based on Pick Lists

[Ryan180]

I work for an insurance brokers who sell policies on behalf of a panel of insurers. I've been given a spreadsheet containing sales data where each individual row represents an individual sale, and each column represents a different element of each such sale.

For example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Insurer[/TD]
[TD="align: center"]Area[/TD]
[TD="align: center"]Age Category[/TD]
[TD="align: center"]Cover Type[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer1[/TD]
[TD="align: center"]West Midlands[/TD]
[TD="align: center"]30 to 39[/TD]
[TD="align: center"]Comprehensive[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]350.00[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer1[/TD]
[TD="align: center"]Lincolnshire[/TD]
[TD="align: center"]20 to 29[/TD]
[TD="align: center"]Third Party Only[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]250.00[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer2[/TD]
[TD="align: center"]Cornwall[/TD]
[TD="align: center"]50 to 59[/TD]
[TD="align: center"]Comprehensive[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]400.00[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer2[/TD]
[TD="align: center"]Devon[/TD]
[TD="align: center"]60 to 69[/TD]
[TD="align: center"]Comprehensive[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]900.00[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer3[/TD]
[TD="align: center"]West Midlands[/TD]
[TD="align: center"]20 to 29[/TD]
[TD="align: center"]Third Party Fire & Theft[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]600.00[/TD]
[/TR]
[TR]
[TD="align: center"]Insurer3[/TD]
[TD="align: center"]Berkshire[/TD]
[TD="align: center"]30 to 39[/TD]
[TD="align: center"]Comprehensive[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]300.00[/TD]
[/TR]
</tbody>[/TABLE]

I need to create a report based on this data that will allow the user to quickly establish the average premium per insurer based on certain criteria, preferably using drop-down lists.

For example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Insurer[/TD]
[TD]Category[/TD]
[TD]Value[/TD]
[TD]Average Premium[/TD]
[/TR]
[TR]
[TD]Insurer1[/TD]
[TD]Area[/TD]
[TD]West Midlands[/TD]
[TD]350.00[/TD]
[/TR]
</tbody>[/TABLE]

In the above table, "Insurer" would be a pick list of insurers, "Category" would be a pick list including "Area", "Age Category", "Cover Type" and "Sex", "Value" would contain a list of values based on the category selected under "Category" and "Average Premium" would show the average premium based on the selections in the preceding cells.

I've tried various methods in an attempt to achieve the above using a combination of VLOOKUP, MATCH and INDEX but can never quite seem to get there.

Also, when creating dynamic lists using Data Validation, the lists contain duplicates of each value as opposed to a single instance which can be summarized.

I'm under a bit of pressure to get this report produced, so any assistance you can provide would be much appreciated! I'm essentially after a step-by-step as to how to produce the above, including any necessary formulas.

Thanks in advance!

 

[Andrew Poulsom]

For your formula try:

=SUMPRODUCT(--(A2:A7=H2),--(INDEX(B2:E7,0,MATCH(I2,B1:E1,FALSE))=J2),F2:F7)/SUMPRODUCT(--(A2:A7=H2),--(INDEX(B2:E7,0,MATCH(I2,B1:E1,FALSE))=J2))

where your first table is in the range A1:F7 and your second table is in the range H1:K2.

Or you could, of course, use a pivot table.

 

[Ryan180]

Thanks Andrew, this worked a treat!