+ Reply to Thread
Results 1 to 10 of 10

INDEX SMALL and ROW

  1. #1
    Registered User
    Join Date
    03-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Office 7
    Posts
    5

    INDEX SMALL and ROW

    I've been trying to get this formula to work for days now but can't seem to get it work. Using the following formula I found on the web

    =IFERROR(INDEX($C$7:$J$50001,SMALL(IF(($A$7:$A$50001="Name")*($B$7:$B$50001="Yes or No"),ROW($2:$1001)-ROW($1:$1)),ROW($A1))),"")

    Essentially, my data starts on Row 7 and i'm trying to import the value of a cell if it matches the name in Column A and has either "Yes" or "No" in column B. Also hiding any errors that it may generate.

    Column A
    Names

    Column B
    "Yes/No"

    Column C
    Value I'm trying to import

    I got it to work for a few line items but doesn't work for every name, i'm guessing it has to do with the row function which i have no idea what to do with.

    Help please!

  2. #2
    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 SMALL and ROW

    Try this now

    =IFERROR(INDEX($C$7:$J$50001,SMALL(IF(($A$7:$A$50001="Name")*($B$7:$B$50001={"Yes","No"}),ROW($2:$1001)-ROW($1:$1)),ROW($A1))),"")

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

    However, there are other parts of the formula that seems to be problematic. I think the array formula should this:

    =IFERROR(INDEX($C$7:$C$26,SMALL(IF(($A$7:$A$26="Name")*($B$7:$B$26={"Yes","No"}),ROW($A$7:$B$26)-ROW($A$7)+1),ROWS(C$7:C7))),"")
    Last edited by AlKey; 03-11-2015 at 06:01 PM.
    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

  3. #3
    Registered User
    Join Date
    11-07-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Office 365
    Posts
    19

    Re: INDEX SMALL and ROW

    If the contents of C column is a number, try the following formula:
    =SUMPRODUCT(($A$7:$A$50001="Name")*(($B$7:$B$50001="Yes")+($B$7:$B$50001="No")),$C$7:$C$50001)

  4. #4
    Registered User
    Join Date
    03-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Office 7
    Posts
    5

    Re: INDEX SMALL and ROW

    This worked for the first two names I input but after that just returning blanks.

    Quote Originally Posted by AlKey View Post
    Try this now

    =IFERROR(INDEX($C$7:$J$50001,SMALL(IF(($A$7:$A$50001="Name")*($B$7:$B$50001={"Yes","No"}),ROW($2:$1001)-ROW($1:$1)),ROW($A1))),"")

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

  5. #5
    Registered User
    Join Date
    03-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Office 7
    Posts
    5

    Re: INDEX SMALL and ROW

    Gah, Column C is not a number

  6. #6
    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 SMALL and ROW

    Quote Originally Posted by MAG27 View Post
    This worked for the first two names I input but after that just returning blanks.
    Try the second formula in my first post

  7. #7
    Registered User
    Join Date
    03-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Office 7
    Posts
    5

    Re: INDEX SMALL and ROW

    Hmmm still no luck. Attached is a screenshot of something i mocked up w/ test data. As you can see in C3 i want to enter the name and have those rows that contain that name and "Yes" in Column D populate the color in Column E.


    Excel.JPG

    Quote Originally Posted by AlKey View Post
    Try the second formula in my first post

  8. #8
    Registered User
    Join Date
    11-07-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Office 365
    Posts
    19

    Re: INDEX SMALL and ROW

    The column C is not numerical, try this formula:
    =IFERROR(INDIRECT(ADDRESS(SUMPRODUCT(($A$7:$A$50001="Name")*(($B$7:$B$50001="Yes")+($B$7:$B$50001="No")),ROW($C$7:$C$50001)),3)),"")

  9. #9
    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 SMALL and ROW

    You ranges has changed

    =IFERROR(INDEX($B$7:$B$50001,SMALL(IF(($A$7:$A$50001=$C$3)*($D$7:$D$50001={"Yes","No"}),ROW($A$7:$B$50001)-ROW($A$7)+1),ROWS(A$7:A7))),"")
    Attached Files Attached Files
    Last edited by AlKey; 03-11-2015 at 06:23 PM.

  10. #10
    Registered User
    Join Date
    11-07-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Office 365
    Posts
    19

    Re: INDEX SMALL and ROW

    Try this formula: {=IFERROR(INDEX($B$7:$B$50001,SMALL(IF(($A$7:$A$50001=$C$3)*(($D$7:$D$50001="Yes")+($D$7:$D$50001="No")),ROW($A$7:$B$50001)-ROW($A$7)+1),1)),"")}

+ 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. INDEX and SMALL help
    By markgilmore in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-02-2015, 01:02 PM
  2. [SOLVED] Index small array
    By namluke in forum Excel General
    Replies: 3
    Last Post: 03-12-2014, 06:00 PM
  3. Index( Match( Small( If
    By TravCAH in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2013, 08:41 AM
  4. Index/Small help
    By WWSL14 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-12-2013, 03:50 PM
  5. [SOLVED] Trying to do an Index with small if for the first time
    By Cobbhill in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-04-2012, 03:32 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