VLookUp containing Offset error

[Wayne's Wisdom]

Hi All,

Using Office Jhome and Business 2010 and Windows 7 Pro.

I found similar questions but they don't fit my scenario. In the long run this will be part of a macro but I first need it to work as a formula in a cell. I don't know if this is the best way or even a suitable method but it's the only solution i can think of.

I have a sheet that will calculate the commission due for staff. The staff member may at some point be upgraded to a new commission structure or percentage but payment is calculated around the time the business was introduced.

First I have a staff index sheet, column A is staff Id, Column B counts from 1 (Currently over 400 and growing)
[TABLE="width: 167"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Staff ID[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100004[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]100007[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]100010[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]100016[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]100019[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]
Then I have a LookUp sheet. Column B is a regular vlookup. Column C countifs for staff Id. Column D is start date at company. Column D is a date on which their commission changed to a new structure. Column E is blank for most(only used when staff member leaves company). Column F is a commission code reference that looks up the commission formula to be used. Column G is currently copied from the third sheet. The third sheet is a report of sales and another column will take the data from column G do a lookup for the commission formula and then calculate the commission to be paid. The date of the business done determines the commission to be paid. If a deal happened a year ago and commission is now due then the commission paid is according to the code they were on when the business was agreed to and so needs to reflect the code of that period.
[TABLE="width: 1101"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1176"]
<colgroup><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staff Id[/TD]
[TD]Index[/TD]
[TD]NumberofArrangements[/TD]
[TD]Start Date[/TD]
[TD]Comm Change Date[/TD]
[TD]Leave Date[/TD]
[TD]CommissionCode[/TD]
[TD]PolicyCommCode[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2011/05/17[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100007[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100010[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100016[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100019[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]100022[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD="align: right"]2010/05/17[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On report sheet each row represents a deal and takes the staff Id and looks for the commission code on lookup sheet. it is dependent on the date of the business.

Eg. businees done on 2010/12/01 for Id 100001 should yield "1" (column F) but for the same Id business done on 2011/12/01 should yield a "2".

Formulas on sheet 2:
Column A: None
Column B: =VLOOKUP(A2,Index_Sheet!$A:$B,2,FALSE)
Column C: =COUNTIFS(A:A,A2)
Column D - F: None
Column G: =VLOOKUP(K2,OFFSET(LookUPSheet!$E$2,B2-1,0,C2,3),3,TRUE)

I think the problem lies in my offset row. I get the wrong code or an #N/A error.

Please assist me.
Wayne

 

[mfexcel]

why you are using TRUE in the vlookup? are you intended to have approximate match? if that's the case, the data in Column E should be sorted in ascending order.

If you need exact match actually, change the TRUE to FALSE

 

[Wayne's Wisdom]

Deleted

 

[Wayne's Wisdom]

I am using true because it's a date and I am looking for the date (of the comm structure) which is the closest preceding date to the date of the business. So basically I am looking for the comm structure of a staff Id that was valid before the date of the transaction.

Also, I assume you are correct and that's why I have sorted the data in COLUMN A in ascending. The dates for a particular Id will automatically be sorted ascending. The Offset needs to only look at the rows of the particular Id in question.

 

[Wayne's Wisdom]

To elaborate:
E.g.
Id 100013 starts at company on 2010/01/01 and is paid on comm structure 7
Gets upgraded to comm structure 2 on 2011/01/01
Gets upgraded to comm structure 5 on 2012/01/01
Today business is concluded and commission is due but the date of transaction is in the first year of employment so the commission is paid on structure 7, a second deal was also concluded today but the transaction date is in the second year of employment so the structure should be that of structure 2. any business from year 3 until today will be calculated as code 5. The array then needs to look at the 3 rows of that id and select the code of the row which the date of transaction precedes that of column E (i.e between dates of column D and E).