hate to double post but have not really got a good answer, more lo

[dlotz]

I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid, an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise

 

[T. Valko]

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D110,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns: 401912

 

[T. Valko]

You **might** be able to use this normally entered version **if** the office
code is always a numeric value.

=SUMPRODUCT(--(A1:A10=G1),--(E1:E10=H1),D110)

The other array version is more flexible as it handles any data type.

 

[dlotz]

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( <> less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is > the office code date it will produce the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008

 

[Shane Devenshire]

Hi,

You problem is going to be that you really aren't looking at a specific date
but a range of dates, so you are going to need to create another table which
associates dates and agent #. This will only work if this isn't completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1>=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1

 

[dlotz]

so would I have a two entry on each agent #, and sort by agent #
or does sorting even matter

ie
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910
and then the next agetn and so on,
And then to make it even more complex, do I need to have an ending
date???? or should I carry the entry way out and the adjust it to suit.

Please advise

 

[T. Valko]

Can you better explain what you date matching criteria is?

 

[dlotz]

OK the basic way it works is the agent # gets paid for some form
of production, when the agent # get paid a portion of the payment goes
to the office code,
The agent # will never change, the office code is more like a manager
hierachy.
So lets say BOB started on
JAN 1 2008, his agent # is 1234 and is under office code 4567
so if he did somehting that would generate lets say $100.00
$50.00 would go to the agent # and 50.00 would go to the office code.

So on 5-15 we decide to close the office code 4567 and start a new profit
code 8910

OK so to make it easy.
The column would be
a: Agent #
b: writting date
c: office code.

I need the correct office code to fall the in the range of dates the agent #
acctually
dated the produciton.
was profiled to that codeso If the writting date were 2-25, or 5-09 the office code produced would
be 4567
if the writting date were 6-09 or 9-27 the off code would procude 8910
SO the writting date would random, the agent # will stay unique

 

[T. Valko]

Ok, if you have a date range then:

G1 = agent lookup number
H1 = start date
I1 = end date

Array entered** :

=INDEX(D110,MATCH(1,(A1:A10=G1)*(B1:B10<=H1)*(C1:C10>=I1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.