+ Reply to Thread
Results 1 to 7 of 7

1 Criteria when matched to display multiple rows of result

  1. #1
    Registered User
    Join Date
    08-06-2017
    Location
    Oman
    MS-Off Ver
    2013
    Posts
    2

    1 Criteria when matched to display multiple rows of result

    Test..jpg

    I need help to display multiple records that have the mobile number 888888 in different rows.

    My database is A1:I8
    Search Criteria is C13

    Results corresponding to C13 to be displayed from row 16 onwards.

    I hope i could explain well.

    Thank you in advance
    Attached Files Attached Files
    Last edited by Heena Suleman; 08-06-2017 at 11:10 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: 1 Criteria when matched to display multiple rows of result

    In A16

    =IFERROR(INDEX(A$2:A$8,SMALL(IF($I$2:$I$8=$C$13,ROW($A$2:$A$8)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy across and down

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: 1 Criteria when matched to display multiple rows of result

    Hi Heena,

    Your problem can be done using Excel's Advanced Filter tool. Read about it at:

    http://www.contextures.com/xladvfilter01.html

    You will need to put your 8888 UNDER the "Model Number" and check the "Copy To" in the Advanced Filter dialog.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: 1 Criteria when matched to display multiple rows of result

    see attached worked example of advanced filter as suggested by MarvinP
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: 1 Criteria when matched to display multiple rows of result

    or a VBA alternative
    Values appear automatically when value in I13 amended
    (makes use of hidden sheet "List")

    VBA is in SHEET module:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-06-2017
    Location
    Oman
    MS-Off Ver
    2013
    Posts
    2

    Re: 1 Criteria when matched to display multiple rows of result

    Thank you so much

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: 1 Criteria when matched to display multiple rows of result

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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] Input result if a criteria is not matched from range
    By jamewoong in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2015, 02:15 AM
  2. Search the value in multiple sheets and display the entire row of matched value in list bo
    By haridevadiga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2015, 08:08 AM
  3. Replies: 2
    Last Post: 07-13-2015, 07:27 PM
  4. [SOLVED] Search for multiple rows from multiple sheets by criteria & show result in userform
    By ANDREAAS in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-07-2014, 11:29 AM
  5. Replies: 4
    Last Post: 03-18-2014, 11:50 AM
  6. How to sum multiple rows horizontally with the matched criteria
    By Cute_Petals in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-15-2013, 01:21 PM
  7. [SOLVED] Search multiple sheets for criteria and paste all matched rows in new sheet
    By BertLady56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2012, 01:09 PM

Tags for this Thread

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