+ Reply to Thread
Results 1 to 6 of 6

INDEX formula error - using SMALL, ROW, MIN

  1. #1
    Registered User
    Join Date
    11-07-2016
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    36

    INDEX formula error - using SMALL, ROW, MIN

    I have a formula that is meant to go through a column of data and pull every match to the searched number. I then have an array where it pulls the corresponding data (Looks in column A and returns column C, etc.). So it needs to find the first match and all it's data, the second match and it's data, etc. However right now it is not pulling ALL matches. It is pulling a random (?) number of them. The complex formula is:

    =IFERROR(IF(INDEX(Data!J$2:J$20000,SMALL(IF($A$2=Data!$A20:$A$20000,ROW(Data!$A20:$A$20000)-MIN(ROW(Data!$A$2:$A$20000))+1,"")ROW(Data!$A20)))=0,"Not available",INDEX(Data!J$2:J$20000,SMALL(IF($A$2=Data!$A20:$A$20000,ROW(Data!$A20:$A$20000)-MIN(ROW(Data!$A$2:$A$20000))+1,""),ROW(Data!$A20)))),"")

    The heart of the formula is :
    INDEX(Data!J$2:J$20000,SMALL(IF($A$2=Data!$A20:$A$20000,ROW(Data!$A20:$A$20000)-MIN(ROW(Data!$A$2:$A$20000))+1,"")

    Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX formula error - using SMALL, ROW, MIN

    Quote Originally Posted by KAP123 View Post
    The heart of the formula is :

    INDEX(Data!J$2:J$20000,SMALL(IF($A$2=Data!$A20:$A$20000,ROW(Data!$A20:$A$20000)-MIN(ROW(Data!$A$2:$A$20000))+1,"")
    I didn't download your file.

    If you index the entire column then you don't need to calculate the array of "offset corrections".

    INDEX(Data!J:J,SMALL(IF($A$2=Data!$A$20:$A$20000,ROW(Data!$A$20:$A$20000)),ROWS(A$1:A1)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    08-15-2014
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2021
    Posts
    105

    Re: INDEX formula error - using SMALL, ROW, MIN

    sounds similar to the problem I just had here my post on it

    http://www.excelforum.com/showthread...t=#post4533738

    the answer he gave me helped me

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: INDEX formula error - using SMALL, ROW, MIN

    Here is a shorter formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    11-07-2016
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    36

    Re: INDEX formula error - using SMALL, ROW, MIN

    This worked AlKey, thank you!!! What did you change?

  6. #6
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: INDEX formula error - using SMALL, ROW, MIN

    From KAP123's formulas:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    So, the formulas new:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by tamthat; 12-01-2016 at 02:11 PM.

+ 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] ask formula index+small + if
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2015, 04:29 AM
  2. Index Small Row Formula
    By davidcharis16 in forum Excel General
    Replies: 3
    Last Post: 04-16-2015, 04:40 AM
  3. [SOLVED] VBA instead of index small or vlookup formula
    By cajand in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-25-2014, 05:29 PM
  4. Error using Array: Index, Small, Row to pull list from PivotTable
    By lgnmag in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-25-2014, 08:39 PM
  5. [SOLVED] Index range small row - num error
    By Trax in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-13-2013, 09:18 AM
  6. Can't find the error functions INDEX () and SMALL()
    By HyperG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2012, 12:20 AM
  7. INDEX SMALL ROW Formula #REF! error.
    By elcasey125 in forum Excel General
    Replies: 3
    Last Post: 12-10-2008, 12:41 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