got a tough on and I am confused were to start

[dlotz]

Ok I have a table array that is preety in depth.
It works off of an agent # unique and sortable.

A vlookup formula popluates the agent name and
additional info based off the #.
Ok here is the problem, the agent # is assigned to an office office code.
So in other words the agent produciton is paid into that office code. well
from time to time we re-profile these guy under new office codes.
The code has an inception date, so on
JAN 1st agent 177721 might be under office code 401895 and
then aug he has moved to office code 401913. His producion is dated
as well.
Question, how can I stucture this were data intry for the date of
production will return the correct office code by the codes inception dates??

please ask quesitons if you do no understand mine.

lets say column b is the date of the production and col
c is the agent #, col d-g, are random data agent name ect.
and col. h is the office code.
how can incorpoate the date in the array to return the correct offic code

 

[Joel]

can you place some samples of the data

 

[dlotz]

its going to be hard to do in this format. do you have an email
that I can use.

 

[Joel]

joel dot warburg at itt dot com

its going to be hard to do in this format. do you have an email
that I can use.

 

[smartin]

Ok I have a table array that is preety in depth.
It works off of an agent # unique and sortable.

A vlookup formula popluates the agent name and
additional info based off the #.
Ok here is the problem, the agent # is assigned to an office office code.
So in other words the agent produciton is paid into that office code. well
from time to time we re-profile these guy under new office codes.
The code has an inception date, so on
JAN 1st agent 177721 might be under office code 401895 and
then aug he has moved to office code 401913. His producion is dated
as well.
Question, how can I stucture this were data intry for the date of
production will return the correct office code by the codes inception dates??

please ask quesitons if you do no understand mine.

lets say column b is the date of the production and col
c is the agent #, col d-g, are random data agent name ect.
and col. h is the office code.
how can incorpoate the date in the array to return the correct offic code

I don't understand your layout, but this works.

With "master data" in Sheet2!A2:Cx (AgentID, Office, Inception Date) and
data entry in the current sheet (AgentID, transaction date, and
office=???) this array formula* in C2 and fill down will do it:

=LARGE(IF((A2=Sheet2!$A$2:$A$4)*(B2>=Sheet2!$C$2:$C$4),Sheet2!$B$2:$B$4),1)

*Commit array formula with Ctrl+Shift+Enter (not just Enter).
Adjust range references on Sheet2 to suit.

It is important that the inception date be sorted by inception date
ascending.