You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jose123" wrote in message
...
Thank you, thank you, I misread the instructions.
I wish I could give you two stars for your help!
--
Jose


"T. Valko" wrote:

Does the * mean 'and'?

It's multiplying 2 arrays together but it does mean "and".

IF (A2:A6<=A18) and (B2:B6=B18)

All of the formulas used are array formulas.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

--
Biff
Microsoft Excel MVP


"jose123" wrote in message
...
Your understanding is correct. I tried your example but I'm getting
and
#value error on A2:A6. Does the * mean 'and'? Column A is formatted
as a
date field.

=MAX(IF((A2:A6<=A18)*(B2:B6=B18),A2:A6))

--
Jose


"T. Valko" wrote:

This is somewhat complicated so I would do it in steps.

If I understand what you want:

Find the pay rate of employee X based on the rate and scale of the
closest
date that is *less than or equal to a lookup date*.

As I look at your sample data the "problem" I see is you first have to
find
the closest date for the employee in Data1 then you have to find the
closest
date to that date in Data2.

So, here's how to do this based on my interpretation of what you want.

A1:F6 = Data1
A8:E13 = Data2

Lookup values:

A18 = some date
B18 = employee number

Enter this array formula** in C18. This will return the closest date
from
Data1 that is <=lookup_date:

=MAX(IF((A2:A6<=A18)*(B2:B6=B18),A2:A6))

Format as Date

Enter this array formula** in D18. This will return the closest date
from
Data2 that is <=C18:

=MAX(IF(A9:A16<=C18,A9:A16))

Format as date

Enter this array formula** in E18 and copy across to F18. This will
return
the rate and scale from Data1 for the employee:

=INDEX(D2:D6,MATCH(1,($A2:$A6=$C18)*($B2:$B6=$B18) ,0))

And finally, enter this array formula** in G18. This will return the
pay
rate from Data2:

=INDEX(C9:E16,MATCH(1,(A9:A16=D18)*(B9:B16=F18),0) ,MATCH(E18,C8:E8,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.

--
Biff
Microsoft Excel MVP


"jose123" wrote in message
...
In my template I have the following information. I'd like to get
the
data
out of the template and into a workbook of its own. What would be
the
best
approach to set-up this data? For example, Data1 and Data2 can be
their
own
worksheet in the same workbook and the data is set-up as shown or
should
the
data be in it's own workbook and create a new tab when the effective
date
changes. I don't want to change the formulas in the template
everytime
a
new
tab is created. Also, I need to know how to do a lookup of this
data.

Data1:
Eff. Date EE# EE Name Rate Scale WDays
12/01/08 1 Doe, John R1 100 M-F
01/01/09 1 Doe, John R1 100 W-Sun
12/01/08 2 Rae, Martha R2 80 M-F
03/01/09 2 Rae, Martha R2 90 M-F

Data2:
----------Rate-----------------
Scale R1 R2 R3
12/01/08 100 23.62 23.92 23.62
12/01/08 90 21.26 21.56 21.26
12/01/08 80 18.90 19.20 18.90
12/01/08 70 16.53 16.83 16.53
03/01/09 100 23.72 24.92 23.82
03/01/09 90 21.46 21.76 21.36
03/01/09 80 19.90 19.30 18.95
03/01/09 70 16.63 16.93 16.73

On 12/05/08 I look up information for EE#1 from data1 and data2 I
would
want
information from the row eff. 12/01/08 to be returned. R1 and 100
would
be
used for the lookup from data2 as wellas the eff. date.
On 04/01/09 I look up information for EE#2 from data1 and data2 I
would
want
information from the row eff. 03/01/09 to be returned. R2 and 90
would
be
used for the lookup from data2 as well as the eff. date.


--
Jose