+ Reply to Thread
Results 1 to 2 of 2

Get all list based on criteria

  1. 09-10-2017, 02:10 PM #1
    hazzaska
    hazzaska is offline
    Registered User
    Join Date
    04-11-2012
    Location
    Stoke, England
    MS-Off Ver
    Ms 365
    Posts
    68

    Get all list based on criteria

    Hi

    I have the following list in columns a & b if i put "Kinsley" in c1 how do i then lookup all the kinsley entries in column a and return their entries from column b populating c2 and downwards?

    Thanks for any help offered


    Belle 2. Midtown Act
    Belle 1. Manifold Liz
    Belle 3. Boldylocks
    Belle 4. Above All Else
    Belle 3. Thistlecrack
    Belle 6. Castlegore Paris
    Belle 2. Holycross Sky
    Belle 1. Chopchop Nellie
    Belle 5. Ballymac Opal
    Belle 1. Sienna Noir
    Belle 5. Mill Cigar
    Belle 4. Goody Gumdrops
    Central 1. Boozed Flash
    Central 4. Small And Dainty
    Central 3. Killuran Lilly
    Central 3. Lenson Wilson
    Henlow 2. Bright Elena
    Henlow 2. Westmead Angelic
    Henlow 2. Savana Richy
    Henlow 4. Savana Jo Jo
    Henlow 5. Clarence Seadog
    Hove 1. Abbodabbo Dasher
    Hove 1. Babyface Violet
    Hove 4. Cloheena Hooley
    Hove 5. Nothing Special
    Hove 6. Mineola Duke
    Hove 4. Chopchop Arthur
    Kinsley 4. Nearly Perfect
    Kinsley 3. Slaneyside Tejay
    Kinsley 4. Colmyard Molly
    Kinsley 3. Barefoot Giada
    Kinsley 3. Wishaw Rebel
    Kinsley 5. Tarmons Holly
    Kinsley 5. Shanty Scorpion
    Kinsley 5. Miss Sprite
    Kinsley 4. Run China Tip
    Kinsley 3. Slaneyside Adele
    Kinsley 6. Almoradi Mai
    Reply With QuoteRegister To Reply
  2. 09-11-2017, 03:34 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Get all list based on criteria

    Use this array formula in C2:
    =IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($C$1,$A$2:$A$39)),ROW($A$2:$A$39)),ROWS(C$2:C2))),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    see sheet.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Use a particular list based on criteria
    By PierceMcGeough in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-02-2016, 11:02 AM
  2. List based on criteria if criteria changes list locks
    By cconlaund in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-23-2015, 11:05 AM
  3. Returning name list based on 2 criteria
    By Ebo12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-28-2014, 03:56 PM
  4. Top Ten list based on an certain criteria.
    By Clark85 in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 10-31-2013, 11:42 AM
  5. [SOLVED] Create a separate list based on criteria in another list.
    By dpitts21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2012, 07:09 PM
  6. COUNTIF help based one two criteria, one being a list
    By ride_op in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-06-2010, 09:24 PM
  7. add to a cell on a list based on two criteria
    By gabrielinlompoc in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-09-2006, 06:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1