+ Reply to Thread
Results 1 to 10 of 10

INDEX / MATCH problem

  1. #1
    Deborah
    Guest

    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)

    Thanks for your help

    Deborah

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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. #3
    bplumhoff@gmail.com
    Guest

    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. #4
    Duke Carey
    Guest

    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)
    >
    > Thanks for your help
    >
    > Deborah


  5. #5
    Richard Buttrey
    Guest

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

  6. #6
    Deborah
    Guest

    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. #7
    bplumhoff@gmail.com
    Guest

    Re: INDEX / MATCH problem

    Hello, Dav,

    That does not work on strings.

    Regards,
    Bernd


  8. #8
    Deborah
    Guest

    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. #9
    Duke Carey
    Guest

    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. #10
    bplumhoff@gmail.com
    Guest

    Re: INDEX / MATCH problem

    Hello Deborah,

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

    Regards,
    Bernd


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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