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