+ 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
    41

    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/MSO2007;Win10/MSO2016
    Posts
    12,570

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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

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

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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)

Similar Threads

  1. Add search results to a single array
    By Scoobster_doo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2017, 01:10 PM
  2. Replies: 2
    Last Post: 03-15-2017, 08:22 AM
  3. VBA returning 3 duplicate search results
    By Rez4ul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2016, 12:23 AM
  4. VBA to search duplicate & return all possible results to Main Sheet?
    By beepbeep_go in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2015, 11:27 AM
  5. [SOLVED] Display search results inluding results that match patrially
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2013, 08:52 AM
  6. Replies: 3
    Last Post: 07-17-2013, 03:41 AM
  7. Search and return all results across all worksheets problem.
    By richcase in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-31-2013, 12:34 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