date range table array formula

[dlotz]

OK I am still trying to figure out an old problem

OK if make the sheet simple
A1 A2 A3
EMP # Date EMP Profile code

TAble array is set up like
L1 M1 N1 O1
Agent # Start Date End Date EMP profile code
177721 1-1-2009 5-31-2009 1234
177721 6-1-2009 12-31-2009 5678

Ok so the big issue is the emp will chage profile peroidically
thoughout a calander year.
I am trying to get were in A1 is you enter the EMP # and
then in B1 you enter a random date the correct profile code
will come up in C1 based on the date range in the of table array
the emp was profiled to the code

=SUMPRODUCT(--(A1=L1:L9),--(B1>=M1:M9),--(B1<=N1:N9),O1:O9)
if not woeking me only, it sticks a 0 regardless of the emp and date entered.

completely open to re-structering the array is needed. As matter of fact the
table array is a work in progress

 

[Ashish Mathur]

Hi,

Try this. Please adjust the range as desired.

SUMPRODUCT((C6:C7=C9)*(D67<=D9)*(E6:E7>=D9),F6:F7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[dlotz]

i guess I am cunfused as to the range one would =C9
and range 2 would = D9 same for the third.
If my data wher typed in A1 B1 and the formula to
produce in C1

 

[Max]

=SUMPRODUCT(--(A1=L1:L9),--(B1>=M1:M9),--(B1<=N1:N9),O1:O9)

.. it sticks a 0 regardless of the emp and date entered.

Your expression is ok. But probably the return col O contains text nums,
which sumproduct cannot evaluate.

You could try this more generic index/match equivalent (normal ENTER to
confirm will do, like the sumproduct) which will work regardless of whether
its nums, text or mixed data in the return col:
=INDEX(O2:O9,MATCH(1,INDEX((A1=L2:L9)*(B1>=M2:M9)*(B1<=N2:N9),),0))

Do high-five this response by clicking the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[dlotz]

question???
In the table you have emp #/ start / end date/ code
what will happen if the end date it left blank.
Will this mess up the function

 

[Max]

what will happen if the end date left blank
Blanks are evaluated as zeros in comparisons,
these would mess up correct comparisons
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik