+ Reply to Thread
Results 1 to 4 of 4

How to return the data in a row matching search criteria

  1. #1
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135

    How to return the data in a row matching search criteria

    I have a workbook with three worksheets. Two of the worksheets have data in exactly the same layout starting with a surname in column A. The third worksheet is a search page. I have already created a cell where I can enter the search criteria (a surname) and in the adjacent cell it returns the number of instances of that surname. I would also like it to return the whole row of each instance of that surname in a table. I have looked at INDEX/MATCH but can't workout how to automatically add another row to the results. I think this is possible?!
    I have uploaded a file TEST.xlsx (I think!)
    Any help would be much appreciated.

    Geoff
    Attached Files Attached Files
    Geoff Culbertson
    Petersfield, UK

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How to return the data in a row matching search criteria

    I've used column J in the two Volume sheets to act as a helper column, to help identify records which match the criteria and give a unique sequential number to them. I put zero in cell J1 of Volume_I and this formula in J2:

    =IF(A2=MENU!$C$4,MAX(J$1:J1)+1,"-")

    This is copied down beyond where your data lies, in order to accommodate more records being added - the hyphens help to show where the formula is active, but you can also see on that sheet that the numbers 1 and 2 are identifying two records which match the criteria (Jones). We want to continue this numbering onto the other sheet, so I have put this formula in cell J1 of Volume_II:

    =MAX(Volume_I!J:J)

    and the formula in J2 is the same as above.

    In the MENU sheet I put this formula in cell B12:

    =IFERROR(INDEX(Volume_I!A:A,MATCH(ROWS($1:1),Volume_I!$J:$J,0)),IFERROR(INDEX(Volume_II!A:A,MATCH(ROWS($1:1),Volume_II!$J:$J,0)),""))

    and this can be copied across into C12:E12 (well it could go further, but you don't have any data for those columns), and then the formulae can be copied down as far as you think you may need them.

    All you need to do now is change the name in C4, and the results will automatically change.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135

    Re: How to return the data in a row matching search criteria

    Pete_UK, Thanks very much, that works beautifully in your example. I just have to copy the formulae into the real workbook, hopefully without problems!
    Regards

    Geoff

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How to return the data in a row matching search criteria

    Glad to help, Geoff.

    Don't forget that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Replies: 8
    Last Post: 05-08-2017, 09:17 PM
  2. return data based on matching 2 criteria
    By jsboss in forum Excel General
    Replies: 4
    Last Post: 06-29-2016, 11:29 PM
  3. Search from provided data list and return all instances of given criteria
    By dave1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2012, 07:01 AM
  4. Search from provided data list and return all instances of given criteria
    By dave1983 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2012, 06:00 PM
  5. [SOLVED] Return cell values for non-matching & matching criteria
    By jenz_skallemose in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-13-2012, 11:52 AM
  6. [SOLVED] Return Data on Search Criteria
    By RicktheBlade in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2012, 06:43 PM
  7. Search Folders and Return Copy files matching multiple criteria
    By akq125 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 11:56 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