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