# INDEX / MATCH problem

1. ## INDEX / MATCH problem

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)

Deborah

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

3. ## Re: INDEX / MATCH problem

Hello Deborah,

Your formula looks ok to me.

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

HTH,
Bernd

4. ## RE: INDEX / MATCH problem

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)
>
>
> Deborah

5. ## Re: INDEX / MATCH problem

On Fri, 12 May 2006 06:16:02 -0700, Deborah
<Deborah@discussions.microsoft.com> 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)
>
>
>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
__________________________

6. ## RE: INDEX / MATCH problem

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

7. ## Re: INDEX / MATCH problem

Hello, Dav,

That does not work on strings.

Regards,
Bernd

8. ## Re: INDEX / MATCH problem

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

"bplumhoff@gmail.com" wrote:

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

9. ## RE: INDEX / MATCH problem

=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

10. ## Re: INDEX / MATCH problem

Hello Deborah,

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

Regards,
Bernd

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1