+ Reply to Thread
Results 1 to 4 of 4

using a lookup formula to find all rows that match same criteria

  1. 12-05-2014, 05:15 PM #1
    nicki_rae22
    nicki_rae22 is offline
    Forum Contributor
    Join Date
    04-02-2013
    Location
    West Palm beach, Fl
    MS-Off Ver
    Excel 2010
    Posts
    113

    using a lookup formula to find all rows that match same criteria

    Hello,

    I have a census. I need to break my census down by therapist specific data on separate worksheets (a worksheet for each therapist). That way they can print out their very own personalized census! I know how to use index match to find the first row that matches my criteria, but I can't figure out how to get it to find the rest of the data for that specific therapist. Maybe a unique identifier? The bed # might work, but I don't know how to incorporate it into my formula for it to work. Help!
    Attached Files Attached Files
    Last edited by nicki_rae22; 12-05-2014 at 05:17 PM.
    Reply With QuoteRegister To Reply
  2. 12-05-2014, 05:32 PM #2
    kbryant414
    kbryant414 is offline
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: using a lookup formula to find all rows that match same criteria

    There's a simple trick I've used at work for this. You can add this to a hidden column in your data table. I started it in cell A6 of your Census tab.

    Please Login or Register  to view this content.
    What this does is give you the name of the therapist, and a count of which instance you are on.

    So, "Rachel 1", "Rachel 2" and so on. You can then do your lookup against that value like so, as long as you add numbers to reference in column A:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 12-05-2014, 05:39 PM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: using a lookup formula to find all rows that match same criteria

    One easy way, in Census column Q I added this formula copied down

    =K6&"_"&COUNTIF($K$6:$K6,K6)
    You can hide that column if you want.

    Then you will be just searching for the therapist's name _1, _2, etc.
    So in Rachel!B6 copied across and down

    =IFERROR(INDEX(Census!$B$5:$P$68,MATCH(Rachel!$F$2&"_"&ROWS($A$1:$A1),Census!$Q$5:$Q$68,0),MATCH(Rachel!B$5,Census!$B$5:$P$5,0)),"")
    Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 12-09-2014, 10:38 AM #4
    nicki_rae22
    nicki_rae22 is offline
    Forum Contributor
    Join Date
    04-02-2013
    Location
    West Palm beach, Fl
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: using a lookup formula to find all rows that match same criteria

    Hello,

    sorry about the delay! Thank you both for the assistance! I figured it was a helper column issue! It works! I definitely learned something today, thanks a bunch! :-)
    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. Help : Multi criteria with MATCH or LOOKUP formula
    By clonedarkman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 10:47 AM
  2. [SOLVED] Formula that counts rows that match 2 criteria, but do not match another
    By VivatMartin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 07:43 AM
  3. find greatest value less than criteria (lookup/match?), use relative cell values
    By jinbman in forum Excel General
    Replies: 1
    Last Post: 07-27-2012, 02:37 PM
  4. Use a lookup/index-match with multiple criteria to find a value
    By jdeumer in forum Excel General
    Replies: 3
    Last Post: 07-11-2009, 02:58 PM
  5. Copy Rows if Lookup Criteria Match
    By vdoubleu in forum Excel General
    Replies: 0
    Last Post: 03-29-2005, 03:06 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