+ Reply to Thread
Results 1 to 13 of 13

Problem with Index and Small functions to lookup and display multiple cells

  1. #1
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Problem with Index and Small functions to lookup and display multiple cells

    Hi All,

    I am unable to upload a test sheet at the moment but Imagine Sheet 1 has 2 columns, Column A lists AREA numbers (i.e AREA A, AREA B etc), Column B lists the branch numbers in those area's (I.e EB101, RF230, CD605).
    On sheet 2 i have a Cell (C2) where i want to enter an AREA number and then on the same sheet in B7:B20 i am trying to put an equation in that will list all the Branch numbers in that Area, I have started using the SMALL function in order to get the row numbers...

    =SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)),ROW(1:1))

    i am pretty sure i need to use the INDEX funtion at the beggning in order to show the contents of the Cell that the Small function has found the row number for but have no idea how to get it to work.

    Any idea's?

    Thanks

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Try
    =INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)-1),ROW(1:1)
    Note that I added a "-1" to your formula also as you are starting with row 2.
    This is an array formula and must be entered with CNTRL SHFT ENTER
    Questions?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Quote Originally Posted by ChemistB View Post
    Try
    =INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)-1),ROW(1:1)
    Note that I added a "-1" to your formula also as you are starting with row 2.
    This is an array formula and must be entered with CNTRL SHFT ENTER
    Questions?
    Brilliant that worked well, I was doing similar but didnt put the -1 in which is probably why the best result i was getting was 0.

    Also after dragging the equation to the cells below in order to list all the matching critea, the cells that cannot find a match have #num, can i get rid of this? maybe using the IF(ISERROR equation?

    Thanks

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Yes,

    IFERROR(=INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)-1),ROW(1:1),"")
    Glad it's working for you.

  5. #5
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Quote Originally Posted by ChemistB View Post
    Yes,

    IFERROR(=INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)-1),ROW(1:1),"")
    Glad it's working for you.
    Nope for some reason that did not work, It seems to just have the complete equation written in the cell, should it have an = at the beginning in order to tell excel that it is an equation?

    Thanks for the help.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Yes, you would need the = sign in front of what I showed in post 4 for it to work. Does that work properly now?

  7. #7
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Quote Originally Posted by ChemistB View Post
    Yes, you would need the = sign in front of what I showed in post 4 for it to work. Does that work properly now?
    Nope still not working, i get "the formula you typed contains and error".

    This is what i am typing..


    =IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$C$2,ROW(Sheet1!$A$2:$A$100)-1),ROW(1:1),"")

  8. #8
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Hello,

    You missed some parentheses, try this one instead
    Please Login or Register  to view this content.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  9. #9
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Quote Originally Posted by Lemice View Post
    Hello,

    You missed some parentheses, try this one instead
    Please Login or Register  to view this content.
    That worked, Many thanks

  10. #10
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Hi,

    That IFERROR worked fine in my computer with excel 2010 but on my work computer which runs 2003 it doesnt work, I have read that I can use =IF (ISERROR but cannot get it to work.

    Any ideas?

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,344

    Re: Problem with Index and Small functions to lookup and display multiple cells

    So try in B7:

    Please Login or Register  to view this content.
    (With B6 is not blank. If B6 is blank, remove "-1" (red in formula))
    Ctrl-Shift-Enter

  12. #12
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    127

    Re: Problem with Index and Small functions to lookup and display multiple cells

    Quote Originally Posted by bebo021999 View Post
    So try in B7:

    Please Login or Register  to view this content.
    (With B6 is not blank. If B6 is blank, remove "-1" (red in formula))
    Ctrl-Shift-Enter
    Brilliant, I will try this when at work, Any chance in giving me a breakdown on what that equation is actually doing?

    Thanks

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,344

    Re: Problem with Index and Small functions to lookup and display multiple cells

    This is a long story, so it is better to upgrade to 2007+

+ 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