software

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(B2amp;C2,sheet1!$A$2:$A $6amp;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,
Bernd2 things:

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

quot;Deborahquot; wrote:

gt; Sheet 1 has 3 columns: grade, step, NBS
gt; Sheet 2 has 3 columns: SM, grade, step
gt; I want to add a column NBS to sheet 2 for that exact grade and step, from
gt; the data on sheet 1.
gt;
gt; I tried the following formula but it gives me #value!.
gt;
gt; =INDEX(sheet1!$C$2:$C$6,MATCH(B2amp;C2,sheet1!$A$2:$A $6amp;sheet1!$B$2:$B$6,0),3)
gt;
gt; Thanks for your help
gt;
gt; Deborah

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

gt;Sheet 1 has 3 columns: grade, step, NBS
gt;Sheet 2 has 3 columns: SM, grade, step
gt;I want to add a column NBS to sheet 2 for that exact grade and step, from
gt;the data on sheet 1.
gt;
gt;I tried the following formula but it gives me #value!.
gt;
gt;=INDEX(sheet1!$C$2:$C$6,MATCH(B2amp;C2,sheet1!$A$2:$ A$6amp;sheet1!$B$2:$B$6,0),3)
gt;
gt;Thanks for your help
gt;
gt;DeborahMy 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 A1100
and your sheet two fields are in A1:E1 with E1 being the NBS

in sheet2 E2 put

=Vlookup(A1,Sheet1!A1100,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: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=541543The ctrl-shift-enter does not seem to work... Is there any other way?

Thanks

quot;Duke Careyquot; wrote:

gt; 2 things:
gt;
gt; 1) You need to enter it with Ctrl-Shift-Enter
gt; 2) drop the quot;,3quot; at the end of the formula
gt;
gt; quot;Deborahquot; wrote:
gt;
gt; gt; Sheet 1 has 3 columns: grade, step, NBS
gt; gt; Sheet 2 has 3 columns: SM, grade, step
gt; gt; I want to add a column NBS to sheet 2 for that exact grade and step, from
gt; gt; the data on sheet 1.
gt; gt;
gt; gt; I tried the following formula but it gives me #value!.
gt; gt;
gt; gt; =INDEX(sheet1!$C$2:$C$6,MATCH(B2amp;C2,sheet1!$A$2:$A $6amp;sheet1!$B$2:$B$6,0),3)
gt; gt;
gt; gt; Thanks for your help
gt; gt;
gt; gt; Deborah

Hello, Dav,

That does not work on strings.

Regards,
BerndHi 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

quot; wrote:

gt; Hello Deborah,
gt;
gt; Your formula looks ok to me.
gt;
gt; You just have to enter it as array formula (with CTRL SHIFT ENTER).
gt;
gt; HTH,
gt; Bernd
gt;
gt;

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

quot;Deborahquot; wrote:

gt; The ctrl-shift-enter does not seem to work... Is there any other way?
gt;
gt; Thanks
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; 2 things:
gt; gt;
gt; gt; 1) You need to enter it with Ctrl-Shift-Enter
gt; gt; 2) drop the quot;,3quot; at the end of the formula
gt; gt;
gt; gt; quot;Deborahquot; wrote:
gt; gt;
gt; gt; gt; Sheet 1 has 3 columns: grade, step, NBS
gt; gt; gt; Sheet 2 has 3 columns: SM, grade, step
gt; gt; gt; I want to add a column NBS to sheet 2 for that exact grade and step, from
gt; gt; gt; the data on sheet 1.
gt; gt; gt;
gt; gt; gt; I tried the following formula but it gives me #value!.
gt; gt; gt;
gt; gt; gt; =INDEX(sheet1!$C$2:$C$6,MATCH(B2amp;C2,sheet1!$A$2:$A $6amp;sheet1!$B$2:$B$6,0),3)
gt; gt; gt;
gt; gt; gt; Thanks for your help
gt; gt; gt;
gt; gt; gt; Deborah

Hello Deborah,

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

Regards,
Bernd