INDEX / MATCH problem

[Guest]

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

 

[bplumhoff]

Hello Deborah,

Your formula looks ok to me.

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

HTH,
Bernd

 

[Guest]

2 things:

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

 

[Richard Buttrey]

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 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
__________________________

 

[Dav]

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

 

[Guest]

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

Thanks

 

[bplumhoff]

Hello, Dav,

That does not work on strings.

Regards,
Bernd

 

[Guest]

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

 

[Guest]

=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.

 

[bplumhoff]

Hello Deborah,

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

Regards,
Bernd