Can you better explain what you date matching criteria is?

--
Biff
Microsoft Excel MVP


" wrote in message
...

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




"Shane Devenshire" wrote:

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
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

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
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,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

--
Biff
Microsoft Excel MVP


" wrote in
message
...
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