Tom sends in today's question. Enter age, gender, lab result and then lookup the rating in a table to indicate if someone's risk is low, moderate, high, or very high. Tom wants to return the result as a color (green, blue, yellow, red) via conditional formatting to the lab result cell. Today's podcast has a little of everything: rearranging the table, exact match, Less than Match, subtracting the number 1, OFFSET, assigning a formula to a defined name, then even a linked picture (aka Camera Tool) lookup for good measure. Bill always says that 99% of his VLOOKUPs end in False. For today's solution, that statistic drops to 25%.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1742.
Risk Factor / 3-Ways Lookup as Conditional Format.
Hey we got a doozy sent in this morning by Tom.
Tom has a table they enter and age and a gender and a ratio and based on that.
So, we figure out the age figure out the gender and then where they fall in this list.
There is a risk factor Low, Moderate, High or Very High and he wants to conditionally format this cell.
This is tough, this is you know, I have to look up.
Well, it's a 3 way look up because you have to look up the age, you have to look up the gender and then figure out where we fall in these ranges, but this table isn't set up very friendly for doing a 3 way look up.
So, I'm going to go on to the next sheet here where I have rewritten Tom's table and I put all of the male on the left hand side, all of the female on the right hand side and then I changed his friendly words here 20 to 29 years to the lower limit 20, 30, 40, 50, 60 to match the groups and I had to change the risk factors.
So, less than .83 I put a 0 and then .83 to .88 point, I just put the lower limit, lower limit here and then greater than .94, I put a new lower limit of .95.
So, I had a recreate Tom's table here in order to do this.
Now, it kind of becomes somewhat, simple that we're just doing really when you get right down to it a one way look up.
We're looking at this .97 in four cells the trick is.
It's gonna be a different fourcells every time and so, I'm going to use a function called OFFSET to figure out where those four cells go.
Now, the OFFSET always specifies a starting location and for me I'm using C15, why C 15 because I know that I'm going to want to go over some number, but I don't wanna have to go down any row.
So, this is kind of the starting row.
So, I'll start from C15 and then I have to figure out how far to go over and to figure out how far to go over it's gonna be a bit of a calculation.
I want to use the MATCH function go look for M or F in this range.
If it's M, it's going to give me a position at 1.
If it's F, it's going to give me a position of 6.
So, I'm either going to get 1 or 6 there.
Let's test that out, I actually haven't tested it before the podcast.
There we go.
So, F gives us a position of 6 and then figure out how far to go over for age, over for age.
Well now, while this match was an exact match the zero we had to find exactly M or F this match is equivalent to a VLOOKUP with true, where we want to find the value just less than.
So, we look up the age in this range and I didn't bother to change the range over here because this identical age ranges, luckily for me So, look for C2 and C14 to G14, when you don't find it give me the value that is just less than.
So, this person here at 38 is the second column.
If they were 58 then they would be over in the fourth column.
All right so, we start from C15 we go over 6 cells to get to the female go over 4 cells to get to the right age, but then we're one too many or one too many.
So, actually from here we go one, two, three, four.
So, we have to subtract 1 from that whole thing.
All right so, here we go, ready?
So, we start from equal MATCH of this ratio press F4 comma the lookup array is going to be the offset of this starting cell.
I'll press F4, how many rows down, no rows down, how many columns over, okay! Oh, that's where we have all of these different pieces that we have to put together.
We're going to do the match of the gender, F4 comma in this range, F4 and we want an exact match there so, we'll use the zero plus the match of the H, F4 comma in this range F4 this time we want a less than match the true version of VLOOKUP and then minus 1 because we're always going over one to many columns.
So, that's how many columns over and then how tall is the table, the table is four rows tall, one column wide.
All right so, that finishes our OFFSET and then in that MATCH we are doing a less than match the true version of VLOOKUP which is equivalent to a one and that whole big thing there should get us the correct position in the risk here, Low, Moderate, High or Very High, Low, Moderate, High or Very High.
All right so, let's just do a test we will change back to male, back to 38 and .88 and they are risk factor of two some male 38.8 falls into two let's try .78 should be a risk factor of 1.
Alright! so, we've now got this form the returning the one, two, three or four, fours and I was surprised Tom had actually gone through and he had tried this before apparently because he was using a helper cell at B21 and I was using a helper cell of C20.
So, let's just keep Tom's conditional formatting there for right now, I will copy this i need everything to point to the exact same spot and so, I used dollar signs throughout.
Yes, yes I did. Alright so, control+C, control+V.
All right so, there now the green is working and we can try some other things here so, 1.03, Very High, All right, looks like it's working.
Well now, what if we didn't want this helper cell down here yeah well what we could do is go into this formula will copy the formula to the clipboard including the equal sign control+C come back here Alt+O+D and we're going to edit each of Tom's rules and where he has equal be 21.
I'll delete that and paste in the formula, click OK and repeat, repeat, repeat, repeat, for the others and then we can get rid of the helper cell it's all working right there in that single cell. Now, we still have to keep my version the table instead of Tom's version of the table.
Looks like Tom did some nice border formatting there and I'll leave that up to Tom to go through.
Although, the one thought that I had in something that Tom did not ask for, but I thought would be fun to do you know what if we took this MATCH formula or this MATCH formula.
I have two different ones I decided to show the first one in the podcast and started from us a specific cell.
So, perhaps cell M14 and said that we wanted to go from there down one, two, three or four cells.
So, it actually returns the word down here now you're saying hey that's no good he, he wanted color formatting. Well, watch this trick.
So, I took this formula, the big OFFSET formula and I assigned it to a name.
So, I went to formulas define name and will actually take a look at it here in the name manager I called it rating and it is that big form that i was careful to point to the sheet in every case.
So, instead of just you know M14, I was using final M14 and and so on.
I fully qualified that so, we're going to take this cell control+C. I'm going to go up here and put it next to my rating and I'm going to Home, Paste, Paste as Linked Picture, which gets me a picture of this cell.
All right! now, watch this amazing trick instead of always pointed M14, the pictures point of M14 backspace through that and type my name to formula equal rating and press Enter and now, I'm getting not just the word but a picture of the word so, here we'll change this person to be 68 and pump up to 1.13 and the conditional formatting works, but I'm also getting the word with the right color directly from these cells.
A cool, cool trick there.
Allright! So, hey, I wanna thank Tom for sending this question in and I hope that Tom doesn't mind that I rearrange this table down here, but you know, that's really what we need to do in order to make these lookups work correctly and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1742.
Risk Factor / 3-Ways Lookup as Conditional Format.
Hey we got a doozy sent in this morning by Tom.
Tom has a table they enter and age and a gender and a ratio and based on that.
So, we figure out the age figure out the gender and then where they fall in this list.
There is a risk factor Low, Moderate, High or Very High and he wants to conditionally format this cell.
This is tough, this is you know, I have to look up.
Well, it's a 3 way look up because you have to look up the age, you have to look up the gender and then figure out where we fall in these ranges, but this table isn't set up very friendly for doing a 3 way look up.
So, I'm going to go on to the next sheet here where I have rewritten Tom's table and I put all of the male on the left hand side, all of the female on the right hand side and then I changed his friendly words here 20 to 29 years to the lower limit 20, 30, 40, 50, 60 to match the groups and I had to change the risk factors.
So, less than .83 I put a 0 and then .83 to .88 point, I just put the lower limit, lower limit here and then greater than .94, I put a new lower limit of .95.
So, I had a recreate Tom's table here in order to do this.
Now, it kind of becomes somewhat, simple that we're just doing really when you get right down to it a one way look up.
We're looking at this .97 in four cells the trick is.
It's gonna be a different fourcells every time and so, I'm going to use a function called OFFSET to figure out where those four cells go.
Now, the OFFSET always specifies a starting location and for me I'm using C15, why C 15 because I know that I'm going to want to go over some number, but I don't wanna have to go down any row.
So, this is kind of the starting row.
So, I'll start from C15 and then I have to figure out how far to go over and to figure out how far to go over it's gonna be a bit of a calculation.
I want to use the MATCH function go look for M or F in this range.
If it's M, it's going to give me a position at 1.
If it's F, it's going to give me a position of 6.
So, I'm either going to get 1 or 6 there.
Let's test that out, I actually haven't tested it before the podcast.
There we go.
So, F gives us a position of 6 and then figure out how far to go over for age, over for age.
Well now, while this match was an exact match the zero we had to find exactly M or F this match is equivalent to a VLOOKUP with true, where we want to find the value just less than.
So, we look up the age in this range and I didn't bother to change the range over here because this identical age ranges, luckily for me So, look for C2 and C14 to G14, when you don't find it give me the value that is just less than.
So, this person here at 38 is the second column.
If they were 58 then they would be over in the fourth column.
All right so, we start from C15 we go over 6 cells to get to the female go over 4 cells to get to the right age, but then we're one too many or one too many.
So, actually from here we go one, two, three, four.
So, we have to subtract 1 from that whole thing.
All right so, here we go, ready?
So, we start from equal MATCH of this ratio press F4 comma the lookup array is going to be the offset of this starting cell.
I'll press F4, how many rows down, no rows down, how many columns over, okay! Oh, that's where we have all of these different pieces that we have to put together.
We're going to do the match of the gender, F4 comma in this range, F4 and we want an exact match there so, we'll use the zero plus the match of the H, F4 comma in this range F4 this time we want a less than match the true version of VLOOKUP and then minus 1 because we're always going over one to many columns.
So, that's how many columns over and then how tall is the table, the table is four rows tall, one column wide.
All right so, that finishes our OFFSET and then in that MATCH we are doing a less than match the true version of VLOOKUP which is equivalent to a one and that whole big thing there should get us the correct position in the risk here, Low, Moderate, High or Very High, Low, Moderate, High or Very High.
All right so, let's just do a test we will change back to male, back to 38 and .88 and they are risk factor of two some male 38.8 falls into two let's try .78 should be a risk factor of 1.
Alright! so, we've now got this form the returning the one, two, three or four, fours and I was surprised Tom had actually gone through and he had tried this before apparently because he was using a helper cell at B21 and I was using a helper cell of C20.
So, let's just keep Tom's conditional formatting there for right now, I will copy this i need everything to point to the exact same spot and so, I used dollar signs throughout.
Yes, yes I did. Alright so, control+C, control+V.
All right so, there now the green is working and we can try some other things here so, 1.03, Very High, All right, looks like it's working.
Well now, what if we didn't want this helper cell down here yeah well what we could do is go into this formula will copy the formula to the clipboard including the equal sign control+C come back here Alt+O+D and we're going to edit each of Tom's rules and where he has equal be 21.
I'll delete that and paste in the formula, click OK and repeat, repeat, repeat, repeat, for the others and then we can get rid of the helper cell it's all working right there in that single cell. Now, we still have to keep my version the table instead of Tom's version of the table.
Looks like Tom did some nice border formatting there and I'll leave that up to Tom to go through.
Although, the one thought that I had in something that Tom did not ask for, but I thought would be fun to do you know what if we took this MATCH formula or this MATCH formula.
I have two different ones I decided to show the first one in the podcast and started from us a specific cell.
So, perhaps cell M14 and said that we wanted to go from there down one, two, three or four cells.
So, it actually returns the word down here now you're saying hey that's no good he, he wanted color formatting. Well, watch this trick.
So, I took this formula, the big OFFSET formula and I assigned it to a name.
So, I went to formulas define name and will actually take a look at it here in the name manager I called it rating and it is that big form that i was careful to point to the sheet in every case.
So, instead of just you know M14, I was using final M14 and and so on.
I fully qualified that so, we're going to take this cell control+C. I'm going to go up here and put it next to my rating and I'm going to Home, Paste, Paste as Linked Picture, which gets me a picture of this cell.
All right! now, watch this amazing trick instead of always pointed M14, the pictures point of M14 backspace through that and type my name to formula equal rating and press Enter and now, I'm getting not just the word but a picture of the word so, here we'll change this person to be 68 and pump up to 1.13 and the conditional formatting works, but I'm also getting the word with the right color directly from these cells.
A cool, cool trick there.
Allright! So, hey, I wanna thank Tom for sending this question in and I hope that Tom doesn't mind that I rearrange this table down here, but you know, that's really what we need to do in order to make these lookups work correctly and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
