Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Conditions (2003)

    I want to retrieve a value:


    =INDEX('DATA FEED AT'!$D$3:$BZ$400,MATCH($D33,'DATA FEED AT'!$B$3:$B$75,0),MATCH('TC Scenario'!C$3,'DATA FEED AT'!$D$1:$BZ$1,0))


    Then, I want to evaluate the value. If the value is equal to ‘<Clinical Only>’, then replace the value with the text ‘[Clinical Only. Do not perform this step. Mark N/A]’

    See attachment New Case: E23-25
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditions (2003)

    You could use this monster formula in E23:

    =IF(INDEX('DATA FEED AT'!$D$3:$F$25,MATCH($D23,'DATA FEED AT'!$B$3:$B$25,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$H$1,0))="<Clinical Only>","[Clinical Only. Do not perform this step. Mark N/A]",INDEX('DATA FEED AT'!$D$3:$F$25,MATCH($D23,'DATA FEED AT'!$B$3:$B$25,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$H$1,0)))

    and copy to the other cells as needed.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditions (2003)

    Looking at your data, you could use:
    =SUBSTITUTE(INDEX('DATA FEED AT'!$D$3:$F$25,MATCH($D23,'DATA FEED AT'!$B$3:$B$25,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$H$1,0)),"<Clinical Only>","[Clinical Only. Do not perform this step. Mark N/A]")
    though this is case sensitive.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Conditions (2003)

    Questions/Issues

    Hans’ formula worked great for New Case E31-33.
    For some reason, I keep getting a #N/A in E29. When I had the previous formula

    =INDEX('DATA FEED AT'!$D$3:$W$54,MATCH($D31,'DATA FEED AT'!$B$3:$B$54,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$W$1,0))

    it was pulling the correct data. Now, I get a “#N/A”. In rows F31,32, & 33 there is a “#REF”. THEN, it worked again in Column G

    Then, I tried Rory’s formula in column H (#REF was appearing here as well with Hans’ formula). It worked in Cells H31,32,&33 (corrected the #REF) issue, but it does not work in rows 29 & 35 either
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditions (2003)

    You should adapt the ranges to your actual data - in the previous workbook, you had Reporting Case IDs in B3:B25 on Data Feed AT, in this one they are in B3:B54. You haven't adjusted the formula for that.
    Also, the lookup range is wider, so you must adjust for that too. Try

    =IF(INDEX('DATA FEED AT'!$D$3:$W$54,MATCH($D29,'DATA FEED AT'!$B$3:$B$54,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$W$1,0))="<Clinical Only>","[Clinical Only. Do not perform this step. Mark N/A]",INDEX('DATA FEED AT'!$D$3:$W$54,MATCH($D29,'DATA FEED AT'!$B$3:$B$54,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:W$1,0)))

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Conditions (2003)

    OOOOFTA!

    I should have noticed that!
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •