+ Reply to Thread
Results 1 to 6 of 6

Listing only visible results - INDEX, SMALL & ROW formula

  1. #1
    Registered User
    Join Date
    02-14-2020
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Listing only visible results - INDEX, SMALL & ROW formula

    Hi all,

    I'm an excel novice and I'm trying to find a way for the following formula to only display visible results from the original data source.
    The scenario - I'm trying to list the names of anyone that meets a certain criteria (exam grade), but then I also want to filter by age so that list only displays people (for example) who are 45 - 50 years old. I'm assuming it's a subtotal I need to input?

    Current formula

    =IFERROR(INDEX(Table1[Name],SMALL(IF(Table1[Exam Grade]="Level 1",ROW(Table1[Name])- MIN(ROW(Table1[Name]))+1),ROWS(B$5:B6))),"")

    N.B. this formula currently works at listing names of those who have achieved a level 1 exam grade.

    Any help is greatly appreciated!!

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Listing only visible results - INDEX, SMALL & ROW formula

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    02-14-2020
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Re: Listing only visible results - INDEX, SMALL & ROW formula

    Please find attached the spreadsheet with some dummy data.

    Thank you
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Listing only visible results - INDEX, SMALL & ROW formula

    Hello Bidmas. Welcome to the forum.

    The only way I could make this work (so far) is with a helper column in the Table1. In column F called [Helper]. Its formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in B3:B11 of 'Lists' this formula does not have to be array entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy range B3:B11 and paste into cells E3, H3, K3 and N3.

    Please see attached.

    I have see this done without helper columns. I have not been able to recall how it's done. Perhaps someone here will jump in.
    Dave

  5. #5
    Registered User
    Join Date
    02-14-2020
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Re: Listing only visible results - INDEX, SMALL & ROW formula

    That worked brilliantly - thank you so much!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Listing only visible results - INDEX, SMALL & ROW formula

    You are welcome. Thank you for the feedback and added rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Index Small If Row Column formula not returning all results
    By EmmatheDancer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2015, 06:34 AM
  2. [SOLVED] Index Small If array formula with mulitple criteria returning incorrect results
    By EmmatheDancer in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-08-2015, 12:52 PM
  3. [SOLVED] Using Index match and small to come up with different results from same lookup
    By sirbletchley in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-19-2015, 07:37 PM
  4. [SOLVED] Formula using INDEX, SMALL, and ISERROR functions gives skewed results
    By rspells in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-09-2013, 03:32 AM
  5. Replies: 6
    Last Post: 10-22-2013, 01:48 PM
  6. How to show results of index small if from left to right
    By suton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2013, 06:39 AM
  7. Replies: 2
    Last Post: 06-20-2012, 12:22 PM

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