vlookup in different workbooks with dynamic ranges
 

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

vlookup in different workbooks with dynamic ranges
 

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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

vlookup in different workbooks with dynamic ranges
 

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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

vlookup in different workbooks with dynamic ranges
 

Hi,

sorry meant that every staff has its own unique number !

"steph" wrote:

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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

vlookup in different workbooks with dynamic ranges
 

Ok, now I'm officially confused!

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
Hi,

sorry meant that every staff has its own unique number !

"steph" wrote:

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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

vlookup in different workbooks with dynamic ranges
 

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 ?


"T. Valko" wrote:

Ok, now I'm officially confused!

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
Hi,

sorry meant that every staff has its own unique number !

"steph" wrote:

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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

vlookup in different workbooks with dynamic ranges
 

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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 ?


"T. Valko" wrote:

Ok, now I'm officially confused!

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
Hi,

sorry meant that every staff has its own unique number !

"steph" wrote:

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"steph" wrote in message
...
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