vlookup in different workbooks with dynamic ranges

  • Thread starter Thread starter steph
  • Start date Start date
S

steph

Hello,

I'm trying to make a formula which looks for a certain value in another
workbook. My worksheet in which the formula has to be entered contains the
payroll number and on the basis of this payrollnumber and the code
(101,140,...) a value of a certain month needs to be returned from the second
workbook.
This second workbook has a layout which looks like this :
A B C D E F G
H
name first name payrollnr jan feb mrch apr may

smith john 001/02
101 10 10 20 30 25
135 16 25 45 10 36
140 ...
145
johnson kelly 001/02
101 12 32 25 21 15

135 ...
140
145

Can anyone help me as i'm struggling with index, match and vlookup to make
it somehow right ?

thanks
 
Assuming *every employee has the same payroll number and the payroll number
is **always** in the exact same sequence*.

A2:A13 = first name
B2:B13 = last name
C2:C13 = payroll number
D2:D13 = payroll data
D1:H1 = column headers of month names

Lookup values:

A17 = last name
B17 = first name
C17 = payroll number
D17 = month name

Try this array formula** :

=INDEX(D2:H13,MATCH(1,(A2:A13=A17)*(B2:B13=B17),0)+MATCH(C17,C2:C13,0)-1,MATCH(D17,D1: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.
 
Hello Biff,

The data i entered was not really complete. Meaning that every personnel
member has its own unique member and does not have the same number (as
mentionned in my example) . So what i want in my calculation sheet is f.i.
for payrollnumber 001/02 the total of code 140 in the month of april. So i
first have to define the dynamic range of the personel member and then in
this range xl must look for the number of a specific code, i guess ?

regards

steph
 
Sorry !

I'll try to explain it as good as possible. In 1 workbook i have a line per
payroll member which is known by its unique payroll number. This payroll
number is also used in the second worbook which contains all the relevant
payroll data of a certain period. What i now want to do is to retrieve the
amounts of a certain person in a certain period. However considering the
set-up of workbook 2 as stipulated below, i need to make a dynamic range on
the basis of payroll number.
Is this more clear ?
 
Sorry, still not following you on this. If I could see how your data is set
up out I might be able to figure something out. Can you put together a
*small* sample file (of dummy data) that demonstrates what you're trying to
do? If you want to do that you can send it to me at:

xl can help at comcast period net

Remove "can", remove the spaces and change the obvious.
 
Back
Top