+ Reply to Thread
Results 1 to 5 of 5

Problem using INDEX array extracting results from IF search )duplicate results search)

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    35

    Problem using INDEX array extracting results from IF search )duplicate results search)

    I am trying to create a routine to type in a surname and it will extract multiple rows of results where the name repeats. I have managed to identify the rows where the names appear, but when trying to index the results to extract all the completed cells on each row things are going wrong. I cant see any issues with my VB and am hoping it is something simple.

    I have created a basic sample sheet where the data is in cells A5:E13, my input field is G4 and the results are B17 onwards.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,107

    Re: Problem using INDEX array extracting results from IF search )duplicate results search)

    Notice that A17 returns the row in the worksheet itself, not the row of the table. You must subtract 4 from that value to get the correct table row.Index on the table with the calculated row and the desired column.
    Ben Van Johnson

  3. #3
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,319

    Re: Problem using INDEX array extracting results from IF search )duplicate results search)

    You may try at B17 with CSE

    =IFERROR(INDEX(A:A,SMALL(IF($A$5:$A$13=$G$4,ROW($A$5:$A$13)),ROW(1:1))),"")

    or normal enter

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$5:$A$13)/($A$5:$A$13=$G$4),ROW(1:1))),"")
    Attached Files Attached Files
    Bo

  4. #4
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    35

    Re: Problem using INDEX array extracting results from IF search )duplicate results search)

    Thanks for your help. I am almost finished now, I ave resolved most problems now except rows 2026 onward on sheet one which identify days between -7 and 30 days of the deadline. Is there a way to have then list in ascending order i.e. -7, -5-0,1, 14 etc. along with the columns identifying the names.

    I have uploaded the latest sheet. If any protection is in place there is no password.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,319

    Re: Problem using INDEX array extracting results from IF search )duplicate results search)

    Please try at B2026 Press Ctrl+Shift+Enter and copy down

    =IFERROR(MATCH(SMALL(IF(($B$3:$B$2003<30)*($B$3:$B$2003>-8),($B$3:$B$2003)+ROW($B$3:$B$2003)/10^6),ROWS(A$1:A1)),($B$3:$B$2003)+ROW($B$3:$B$2003)/10^6,),"")
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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