Date range look up with a combined ID code

Stumpy101

New Member
Joined
Jun 5, 2015
Messages
6
Excel 2013 – used but must be compatable with 2007

Table 1 – Data inputed from a report, it’s function is to return the commission from the CommTable. It does this via lookup codes which is generated by combining Policy Type code & Company Code. This is then looked for in the CommTable via a Vlookup.

Table 2 – CommTable, holds the commissions for each company code for each of it’s policy types.

This currently works great (click below)
Data_tables.jpg
[/url][/IMG]



However as of Feb-15 a company code for a certain list of policy types have had their commissions increased.

I have thought about adding start date and end date table 2 (CommTable), and use this somehow in the PolTypeC&CoC(ComLook) codes. However I’ve been coming up blank on how to correctly implement this.

I believe I should combine the table 1 PolTypeC&CoC(ComLook) code with the date of the transaction, and somehow check the date within this new combined code against an updated table 2.

A company can have as many entries as there are policy type hence the unique code looked (about 20 policy types) hundreds of company codes. Table 1 has tens of thousands of rows of data. Before the commission change and after.

Your help would be gratefully appreciated.
 
Last edited:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Stumpy101

New Member
Joined
Jun 5, 2015
Messages
6
Sorry as I'm new to this excel forums - have I given the correct amount of data also are others able to see the inserted image?

Thank you for your time
 

kumar lama

Board Regular
Joined
May 20, 2014
Messages
84
Sorry I am not able to understand what you really looking for, can you post your sample file in here,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

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
Top