+ Reply to Thread
Results 1 to 5 of 5

Conditional format based on 3 way lookup

  1. #1
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Conditional format based on 3 way lookup

    Hi All,

    I'm having trouble coming up with a way to setup a table to do a lookup based on 3 values and then format 1 cell. I'm thinking it just the way the table is setup that needs to change but I can't seem to come up with a way to set it up. Please take a look at the attached and give me some suggestion. Hopefully the sheet/table is self explanatory. Thanks in advance!
    Attached Files Attached Files
    Tom S.
    ↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the left
    If your question is resolved, mark it SOLVED using the thread tools.

  2. #2
    Registered User
    Join Date
    04-29-2012
    Location
    Florida, USA
    MS-Off Ver
    2010, 2013
    Posts
    34

    Re: Conditional format based on 3 way lookup

    Hello,
    I've attached one possible solution. In it, I kept your original table, although that is mostly for an end-user perspective. The table doing the work is newly added at the bottom, which could be hidden if preferred. The lookup process steps through a series, first finding the cells of the corresponding age, then ***, and then the right range of values for risk category. To simplify things from a formula stand-point, each selection range is defined in the name manager and builds on the previous one(s).

    Some very basic guidelines to the solution are included in the workbook, but let me know if want help understanding any of it. Hope that helps!

    Edit: Hah! I can't say the S word above, but obviously I meant M/F.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Conditional format based on 3 way lookup

    I think this could work, it took me a bit to see what was going on but I think I got it now. I know an Index/match would do it but I didn't think about a "helper" cell, I also intended to mention that the top table is the original data and the second table was the one I was trying to modify to make it work. I would also use the an out out of range table, as you suggested, to do the look ups. Thanks a bunch and maybe I should change the sheets to read gender

    If anyone else has an idea please pass it along. if not I will mark the post as solved tomorrow.

  4. #4
    Registered User
    Join Date
    04-29-2012
    Location
    Florida, USA
    MS-Off Ver
    2010, 2013
    Posts
    34

    Re: Conditional format based on 3 way lookup

    I figured your second table was where you started a solution, but left it intact as a comparison. The helper cell was the easiest way that came to mind, especially since a value of 0.88 could be in any of the risk categories, depending on age and gender.

    Depending on if you ever need to change the table, you could also link the presentational table to the lookup table to help make changes easier and all in one place.

    If I could ask, what is this risk measuring?

  5. #5
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Conditional format based on 3 way lookup

    Thanks again, this will work for this table as well as some others I'm working on. The file is for a doctors office and the table in the file is a waist to hip ratio so I assume a higher risk means you're too fat

+ 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