+ Reply to Thread
Results 1 to 7 of 7

List from index function returning next row

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    List from index function returning next row

    I have the following table in my Pasted Data sheet in cells B1:C17

    Fund TransNo
    3 300718
    4 300718
    2 100030472
    4 100030472
    4 100030476
    8 100030476
    4 100030482
    6 100030482
    3 100030601
    4 100030601
    2 100030602
    4 100030602
    4 100030605
    8 100030605
    4 100030606
    6 100030606

    On a second sheet, I'm using the following formula in D2 to find a list of all of the transactions that have a 6 in them. In the formula it's referencing D1 which contains a 6.

    =IFERROR(INDEX('Pasted Data'!$B$2:$C$11219,SMALL(IF('Pasted Data'!$B$2:$B$11219=D$1,ROW('Pasted Data'!$B$2:$B$11219)),ROW(1:1)),2),"")

    I copy the above formula down and it correctly gives me two values, because there are two 6 values in the data. However, it's giving me the value in the next row, instead of the value next to the 6. So the results I'm getting are 100030601 and 0

    I attached a copy of my excel sheet. The problem exists in all the formulas in the Transactions sheet, but you can't see it because sometimes the value of the next row down matches what the result should have been.

    Thanks
    Attached Files Attached Files
    Last edited by The Phil; 09-19-2015 at 12:11 AM.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,131

    Re: List from index function returning next row

    change the B2 to B1 in the formula


    Kind regards
    Leo
    Attached Files Attached Files

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

    Re: List from index function returning next row

    I didn't download your file.

    Index the entire column:

    =IFERROR(INDEX('Pasted Data'!$C:$C,SMALL(IF('Pasted Data'!$B$2:$B$11219=D$1,ROW('Pasted Data'!$B$2:$B$11219)),ROWS(A$1:A1))),"")

    Also, replace ROW(1:1) with ROWS(A$1:A1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: List from index function returning next row

    The Phil, Good evening.

    To use an FILTER at your PASTED DATA is not an option?

    It's very fast and you can use a SUBTOTAL function to sumarize it.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marcílio Lobão

  5. #5
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: List from index function returning next row

    Great, thank you guys!!! This works now.

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

    Re: List from index function returning next row

    You're welcome. We appreciate the feedback!

  7. #7
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    498

    Re: List from index function returning next row

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

+ 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 function in vba not returning result
    By nikedelik23 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-01-2015, 09:39 AM
  2. Index, Match, Large Function returning duplicate names
    By RNeel55 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2013, 04:09 PM
  3. LARGE function handling dupicates returning an index
    By RalphExcel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2013, 09:55 AM
  4. Replies: 4
    Last Post: 11-13-2012, 04:40 PM
  5. Replies: 5
    Last Post: 06-12-2012, 02:53 PM
  6. Index and Match function returning same values
    By Climaxgp in forum Excel General
    Replies: 4
    Last Post: 12-15-2009, 05:59 AM
  7. index function, returning the column values
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2007, 06: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