Lookup formulas

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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&Sheet2!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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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