How to do a lookup using multiple values

[jose123]

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.

 

[T. Valko]

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(D26,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.

 

[jose123]

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))

 

[T. Valko]

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!