+ Reply to Thread
Results 1 to 7 of 7

Index Small Function Pulling Cell Below Desired Cell

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    43

    Index Small Function Pulling Cell Below Desired Cell

    I am trying to determine what my dog is allergic to by analyzing ingredients in foods she did and didn't have an allergic reaction to. I organized the data into a tab with all of the "Good" foods, one with the "Bad" foods, an "Analysis" tab which counts how many times each "Bad" ingredient appears on the "Good" tab and gives each ingredient a weighted value, and a "Results" tab which lists only "Bad" ingredients that are not on the "Good" tab (count of 0). The problem is my "Results" list is pulling the ingredient below the 0 count ingredient. My formula is below and the sheet is attached.

    {=IFERROR(INDEX(Bad!A$2:A$70,SMALL(IF(Analysis!A$2:A$70=0,ROW(Bad!A$2:A$70)),ROWS(A$2:A2))),"")}

    I was trying to pull a list of ingredients that are in each of the "Bad" foods that do not appear on the "Good" tab at all without the interim "Analysis" tab, but couldn't wrap my head around a formula. So if anyone thinks that is an easier solution, I'm all ears.

    Thanks in advance for any help.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Index Small Function Pulling Cell Below Desired Cell

    Not really sure what you are doing here, but I think this is what you want...
    =IFERROR(INDEX(Bad!A$2:A$70,SMALL(IF(Analysis!A$2:A$70=0,ROW(Bad!A$2:A$70)-1),ROWS(A$2:A2))),"")
    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
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Index Small Function Pulling Cell Below Desired Cell

    Does solve the problem, even if it doesn't explain why it doesn't work in the first place.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Index Small Function Pulling Cell Below Desired Cell

    ROW(Bad!A$2:A$70) starts from row 2, but you actually need it to start from row 2 - 1

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

    Re: Index Small Function Pulling Cell Below Desired Cell

    Quote Originally Posted by sskgintl View Post
    Does solve the problem, even if it doesn't explain why it doesn't work in the first place.
    This link contains an explanation of the general formula.

    http://www.excelforum.com/excel-form...-function.html

    However, if you index the entire column then you don't have to "worry" about it!

    =IFERROR(INDEX(Bad!A:A,SMALL(IF(Analysis!A$2:A$70=0,ROW(Bad!A$2:A$70)),ROWS(A$2:A2))),"")

    Still array entered.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Index Small Function Pulling Cell Below Desired Cell

    Thanks. I didn't realize the row function was returning the absolute row of the sheet, not the relative row of the range. When I used this function on another sheet, all of the references were whole columns as the data was being continuously added to.

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

    Re: Index Small Function Pulling Cell Below Desired Cell

    You're welcome. Thanks for the feedback!

+ 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] Problem with function INDEX, SMALL AND IF
    By dejussy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-08-2014, 10:36 PM
  2. Index small if using choice of columns based on cell
    By Trax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2013, 07:24 AM
  3. [SOLVED] Help speed up slow INDEX (SMALL function
    By submariner18 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-12-2012, 01:14 PM
  4. Pulling Color and Value of Cell in Index formula
    By Caitlin.J in forum Excel General
    Replies: 0
    Last Post: 08-15-2011, 03:58 PM
  5. Replies: 1
    Last Post: 04-01-2011, 12:57 PM
  6. Index/Match formula not pulling through desired results
    By Climaxgp in forum Excel General
    Replies: 0
    Last Post: 08-02-2010, 08:14 AM
  7. INDEX SMALL ROW array function
    By ACurtis802 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-10-2009, 02:10 AM

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