+ Reply to Thread
Results 1 to 7 of 7

Returning cell content from array based on two conditions

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    Returning cell content from array based on two conditions

    Hi folks. New to site, glad to have the resource and everyone's help. I have a spreadsheet with patient ID numbers, admission dates and diagnoses, among other information. I've been struggling to come up with the right functions and nesting to return text from a cell based on two conditions being met:
    1. Locate in an array an ID# from a reference cell.
    2. Then locate the admission date from another reference cell.
    3. Go to the row specified from 1. and 2. and the column for diagnosis.
    4. Return the text for that diagnosis for the patient ID# and admission specified.
    I've tried VLOOKUP, INDEX, ROW and others in various nesting combinations and am getting nowhere. I've sorted by ID#, then ADMISSION date. I've attached an example of the spreadsheet. It contains many patients with many different admissions, some may have only one admission, others 7 or more. It basically looks as follows:
    ID# ADMISSION DIAGNOSIS
    1234 1/5/09 CHF
    1234 2/30/09 AFIB
    1234 5/1/10 CHEST PAIN
    3333 5/5/09 DM
    4321 1/4/09 CKD
    4321 10/710 AMS
    etc.
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Returning cell content from array based on two conditions

    in g2 filled down
    =INDEX($C$2:$C$20,MATCH(E2&F2,INDEX($A$2:$A$20&$B$2:$B$20,0),0))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Returning cell content from array based on two conditions

    See the example.

    I used a helpcell to come to the result.

    Also used indirect (which will slow your system, if it are many data).

    Do you have questions, on this item, just ask.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    Re: Returning cell content from array based on two conditions

    This is great! Love this place...thanks.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Returning cell content from array based on two conditions

    a bit unnecessary that oeldere

  6. #6
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    Re: Returning cell content from array based on two conditions

    You both just did in a minute what I couldn't do in hours. Thanks so much. Now I will try to understand the formula.....

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Returning cell content from array based on two conditions

    Please Login or Register  to view this content.
    It is the way, i get to an solution.

    Better this solution, than no solution ad all.

    Your solution is better (you avoid indirect) and so i advice the OP to use your solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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