I have 2 spread sheets:

Weeknr Code amount
4 2001 80.00
4 2002 90.00
5 2001 100.00
5 2003 10.00

2ndt sheet
A B C
1. Week nr 2001 2002
2. 4
3. 4
4. 4

I want to insert a formula in B2:C4 the amount in spreadsheet 1 if code is
equal to B1and C1 and week nr is equal to A2-A4.

Thanks

=INDEX(Sheet1!$C$1:$C$5,MATCH($A2&B$1,Sheet1!$A$1: $A$5&Sheet1!$B$1:$B$5,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Esrei" wrote in message
...
I have 2 spread sheets:

Weeknr Code amount
4 2001 80.00
4 2002 90.00
5 2001 100.00
5 2003 10.00

2ndt sheet
A B C
1. Week nr 2001 2002
2. 4
3. 4
4. 4

I want to insert a formula in B2:C4 the amount in spreadsheet 1 if code is
equal to B1and C1 and week nr is equal to A2-A4.

Thanks

This is my actual formula it returns #value
=+INDEX(Sheet2!D1:D255,MATCH(K1&I2,Sheet2!B1:B255& Sheet2!C1:C255,0))

thanks

"Bob Phillips" wrote:

=INDEX(Sheet1!$C$1:$C$5,MATCH($A2&B$1,Sheet1!$A$1: $A$5&Sheet1!$B$1:$B$5,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Esrei" wrote in message
...
I have 2 spread sheets:

Weeknr Code amount
4 2001 80.00
4 2002 90.00
5 2001 100.00
5 2003 10.00

2ndt sheet
A B C
1. Week nr 2001 2002
2. 4
3. 4
4. 4

I want to insert a formula in B2:C4 the amount in spreadsheet 1 if code is
equal to B1and C1 and week nr is equal to A2-A4.

Thanks

Did you hit ctrl-shift-enter instead of just enter?

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

ps. You don't need that first + either.
=INDEX(Sheet2!D1:D255,MATCH(K1&I2,Sheet2!B1:B255&S heet2!C1:C255,0))
(ctrl-shift-enter)

works fine.


Esrei wrote:

This is my actual formula it returns #value
=+INDEX(Sheet2!D1:D255,MATCH(K1&I2,Sheet2!B1:B255& Sheet2!C1:C255,0))

thanks

"Bob Phillips" wrote:

=INDEX(Sheet1!$C$1:$C$5,MATCH($A2&B$1,Sheet1!$A$1: $A$5&Sheet1!$B$1:$B$5,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Esrei" wrote in message
...
I have 2 spread sheets:

Weeknr Code amount
4 2001 80.00
4 2002 90.00
5 2001 100.00
5 2003 10.00

2ndt sheet
A B C
1. Week nr 2001 2002
2. 4
3. 4
4. 4

I want to insert a formula in B2:C4 the amount in spreadsheet 1 if code is
equal to B1and C1 and week nr is equal to A2-A4.

Thanks





--

Dave Peterson