+ Reply to Thread
Results 1 to 4 of 4

Index Small If Row Column formula not returning all results

  1. #1
    Registered User
    Join Date
    08-28-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    16

    Index Small If Row Column formula not returning all results

    So I have a spreadsheet which should display all students timetables. For some reason it does not return all results. Some work and others don't. I have attached the spreadsheet as it's difficult to explain in words how the whole thing works but basically I type in a class code into AQ3, there is an index array which returns all the names of pupils in that class in AS3:AS11. In column S of the sheet Classes where the raw data is kept there is a formula which returns "yes" if the students name appears in AS3:AS11 of the Timetable sheet. The index array formula which is not working sits in each cell of my timetable, this finds all records in Classes which match a specific day (say Monday), a specific time (say 09:00) and have "yes" in column S. Taking PHA1 as an example Mak, llektra should be appearing in cells B7:B10 and C13:C14 and B17:B18. Only her BIC2 class appears despite the fact that there is a "yes" in every row for her name in Classes. It may be because I tried to sort the data by student then by class group in Classes...

    Can anyone help I desperately need it working by Monday and I'm stumped!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,214

    Re: Index Small If Row Column formula not returning all results

    Change your range to 3100 from 1455,

    As you sorted the data you probably "forgot" that those you were testing appeared in your original range

    Easily done!
    Last edited by JohnTopley; 09-09-2015 at 01:54 PM.

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

    Re: Index Small If Row Column formula not returning all results

    @ EmmatheDancer

    I tried what JohnTopley suggests in the "Monday" grid. I replaced your array-entered formula in B3 of Timetable with this one, filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It returns Mak, Ilektra (and others) for multiple time slots. AS:AS also updates. PHA1 doesn't appear until after row 1700 in Classes.
    Last edited by FlameRetired; 09-09-2015 at 03:33 PM. Reason: clarity
    Dave

  4. #4
    Registered User
    Join Date
    08-28-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    16

    Re: Index Small If Row Column formula not returning all results

    Thank you!!!! I was having a ditzy moment!

+ 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 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
  2. [SOLVED] using SMALL with INDEX MATCHING (not returning any value other than the lowest)
    By forresterbeveridge in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-11-2015, 07:03 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. Returning unique values for different INDEX SMALL references
    By whetu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2014, 08:26 AM
  5. Index and Small Formulas returning #N/A
    By Arnie P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2014, 11:20 AM
  6. [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
  7. 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

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