Conditional format based on 3 way lookup

**Tank997** · 06-29-2013, 12:15 PM

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!

**SteelReyn** · 06-29-2013, 02:44 PM

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.

**Tank997** · 06-29-2013, 04:09 PM

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.

**SteelReyn** · 06-30-2013, 03:06 PM

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?

**Tank997** · 06-30-2013, 04:36 PM

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