Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.

I tried the following formula but it gives me #value!.

=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A $6&sheet1!$B$2:$B$6,0),3)

Thanks for your help

Deborah

Hello Deborah,

Your formula looks ok to me.

You just have to enter it as array formula (with CTRL + SHIFT + ENTER).

HTH,
Bernd

2 things:

1) You need to enter it with Ctrl-Shift-Enter
2) drop the ",3" at the end of the formula

"Deborah" wrote:

Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.

I tried the following formula but it gives me #value!.

=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A $6&sheet1!$B$2:$B$6,0),3)

Thanks for your help

Deborah

On Fri, 12 May 2006 06:16:02 -0700, Deborah
wrote:

Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.

I tried the following formula but it gives me #value!.

=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$ A$6&sheet1!$B$2:$B$6,0),3)

Thanks for your help

Deborah


My standard technique for this sort of task is to use a helper column
and then a VLookup.

So on sheet 1, insert a helper column immediately before the grade
column.
Now concatenate the grade and step into one text string in the helper
column.
Do the same thing on sheet 2

Assuming the sheet1 data with the new helper column is in A1:D100
and your sheet two fields are in A1:E1 with E1 being the NBS

in sheet2 E2 put

=Vlookup(A1,Sheet1!A1:D100,4,false)

and copy down

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

If each grade and step combination only occur once (eg are unique)
=sumproduct((sheet1!$A$1:$a$20=b1l)*(sheet1!$b$1:$ b$20=c1)*(sheet1!$c$1:$c$20))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=541543

The ctrl-shift-enter does not seem to work... Is there any other way?

Thanks

"Duke Carey" wrote:

2 things:

1) You need to enter it with Ctrl-Shift-Enter
2) drop the ",3" at the end of the formula

"Deborah" wrote:

Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.

I tried the following formula but it gives me #value!.

=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A $6&sheet1!$B$2:$B$6,0),3)

Thanks for your help

Deborah

Hello, Dav,

That does not work on strings.

Regards,
Bernd

Hi Bernd,

The ctrl+shift+enter does not work but I entered it as array formula
(INDEX(array,row_num,column_num)).

I can see that the result is correct while entering the formula (before
clicking ok) but the result doesn't show on the spreadsheet...

Thanks
Deborah

" wrote:

Hello Deborah,

Your formula looks ok to me.

You just have to enter it as array formula (with CTRL + SHIFT + ENTER).

HTH,
Bernd

=INDEX(Sheet1!$C$2:$C$6,MATCH(B2&C2,Sheet1!$A$2:$A $6&Sheet1!$B$2:$B$6,0))
entered while holding down the Ctrl and Shift keys works fine for me.

"Deborah" wrote:

The ctrl-shift-enter does not seem to work... Is there any other way?

Thanks

"Duke Carey" wrote:

2 things:

1) You need to enter it with Ctrl-Shift-Enter
2) drop the ",3" at the end of the formula

"Deborah" wrote:

Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.

I tried the following formula but it gives me #value!.

=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A $6&sheet1!$B$2:$B$6,0),3)

Thanks for your help

Deborah

Hello Deborah,

My test was without ",3" as Duke Carey already mentioned. Try his ...

Regards,
Bernd