Dynamic Lookups Based on Pick Lists

Ryan180

New Member
Joined
Nov 6, 2012
Messages
4
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:

InsurerAreaAge CategoryCover TypeSexPremium
Insurer1West Midlands30 to 39ComprehensiveM350.00
Insurer1Lincolnshire20 to 29Third Party OnlyF250.00
Insurer2Cornwall50 to 59ComprehensiveM400.00
Insurer2Devon60 to 69ComprehensiveM900.00
Insurer3West Midlands20 to 29Third Party Fire & TheftM600.00
Insurer3Berkshire30 to 39ComprehensiveF300.00

<tbody>
</tbody>

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:

InsurerCategoryValueAverage Premium
Insurer1AreaWest Midlands350.00

<tbody>
</tbody>

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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top