+ Reply to Thread
Results 1 to 7 of 7

Index() Small() Row()

  1. #1
    Registered User
    Join Date
    07-16-2011
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Index() Small() Row()

    Please see the attached sheet. the Index() Small() Row() formula is working on the sheet but if I want to have some other material on above the table and as soon as I add a new row at top of the data table, the formula does not work properly. Please check the attached sheet and guide me.

    Thanks and regards

    Shakeel
    test sheet.xlsx
    Shakeel Ahmad
    www.facebook.com/shakeelahmad215

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index() Small() Row()

    Are you using Excel 2003 (as your profile says) or 2007+ as you have attached an XLSX file???
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index() Small() Row()

    If you are using Excel 2007+ use:
    =IFERROR(INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2))),"")

    If using Excel 2003 use:
    =IF(ISERROR(INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2)))),"",INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2))))

    Both array entered (CTRL + SHIFT + ENTER)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 10-02-2015 at 05:46 AM. Reason: Entered formula AFTER addition of a few rows, not the AT START formula

  4. #4
    Registered User
    Join Date
    07-16-2011
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Index() Small() Row()

    Quote Originally Posted by Glenn Kennedy View Post
    If you are using Excel 2007+ use:
    =IFERROR(INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2))),"")

    If using Excel 2003 use:
    =IF(ISERROR(INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2)))),"",INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2))))

    Both array entered (CTRL + SHIFT + ENTER)
    It solved my problem. Thank you very much.!.

    Thanks and regards,

    Shakeel

  5. #5
    Registered User
    Join Date
    07-16-2011
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Index() Small() Row()

    Dear Glenn

    =IFERROR(INDEX(C:C,SMALL(IF($A$6:$A$11="ps1",ROW($A$6:$A$11)),ROWS(E$2:E2))),"")

    This code worked too! I have found the formula from net and you corrected it for me but I do not understand it. Is it possible to explain this line that how is this working?

  6. #6
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Index() Small() Row()

    If properly understood.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-16-2011
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Index() Small() Row()

    Thanks Czeslaw for your reply.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Match Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  2. INDEX SMALL and ROW
    By MAG27 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-11-2015, 09:59 PM
  3. INDEX and SMALL help
    By markgilmore in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-02-2015, 01:02 PM
  4. [SOLVED] Index small array
    By namluke in forum Excel General
    Replies: 3
    Last Post: 03-12-2014, 06:00 PM
  5. Index/Small help
    By WWSL14 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-12-2013, 03:50 PM
  6. INDEX SMALL with conditional IF and OR
    By cedarhill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2010, 02:43 PM

Tags for this Thread

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