This duel started out as a simple question - do three lookups and multiply the results for an employee review system. But it evolved into a long discussion about array formulas and can you replace two function arguments with an array. Also along the way, a custom VBA function to solve the problem with a shorter formula.
Transcript of the video:
Bill: Hey. Welcome back. It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 141, multiplication factors based on goals met.
Alright. Today's question is sent in from Gareth at YouTube.
3 categories.
So, for example, category 1, if I scored 0 goals against that I get a multiplication factor of 0.5, 1 goal multiplication factor of 1, 2 goals multiplication factor of 2, and there's 2 other categories but with different multiplication factors.
Alright.
So, I imagine it's something like this.
Category 1, and this is the one he spelled out, if you meet 2 goals, multiplication factor of 2, 1 goal 1, 0 goals 0.5.
So, I created 2 more tables for category 2.
Number of goals, different multiplication factors.
Category 3, number of goals, different multiplication factors, and then, you know, so, he has a table of scores that fall into different categories, but how do I put that into a formula, alright?
So, you know, maybe we have people over here.
It's, like, it's some sort of an employee review system and so on, and people meet various goals or don't meet goals, and how do we score that?
Alright.
So, I suppose we could go really, really fancy, but, the way I see it, we have 3 factors that we're going to multiply by each other.
I can just do that in a single formula here.
So, =VLOOKUP of category 1 in this table, the green table over here, , 2 because I want the second column, and to simplify to change the FALSE to a 0.
FALSE and 0 are the same.
VLOOKUP of the second category, that's 5 here in C12, into this table, the brown table, , 2 , 0 and then * D12, the red table, , 2 , 0 and copy that down.
It does the multiplication and we can probably stack rank these employees based on the number of goals met.
[ =VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0) ] Just kind of old-school, 3 different VLOOKUPs, multiply the VLOOKUPs, nothing fancy here.
Mike, let’s see what you have.
Mike: Thanks, MrExcel.
Hey, I don't really know a different way besides doing 3 LOOKUP functions.
I don't know how to, like, do some array formula lookup, those 3 things all at once.
So, the only thing I can think of to do is switch the lookup function.
Same 3 tables, same everything.
I'm going to notice that since these are goals, we're probably always starting at smallest and going to biggest, and the tables all seem to be taller than they are wider.
That means I can use the LOOKUP function, not VLOOKUP.
LOOKUP only does approximate math.
That's why this needs to be sorted.
It also only does VLOOKUP when the table is the same size or taller than it is wide.
So, I'm going to LOOKUP this, , and then the LOOKUP VECTOR.
Actually, this is the array down here.
I'm simply going to highlight the table and LOOKUP always takes from the last column, so you don't need to put a column number.
I'll hit F4, and then I’ll *, LOOKUP the next one within this table, F4, ). Don’t need to put a column number.
It always takes from the last column, and then LOOKUP this last number right here, , within this third table, F4, ). So, 3 LOOKUPs instead of 3 VLOOKUPs, and then copy it down.
Alright.
Throw it back to MrExcel.
[ =LOOKUP(B12,$J$2:$K$4)*LOOKUP(C12,$J$7:$K$12)*LOOKUP(D12,$J$15:$K$17) ] Bill: Oh Mike.
Come on.
Come on.
You know a lot of us were dreaming that you were going to knock out some sort of SUMPRODUCT with a LOOKUP of the ARRAY of 3 different LOOKUP values into 3 different LOOKUP tables and CONTROL+SHIFT super ENTER and it would all happen, but, of course, that just is fiction.
That would never work.
So… Mike: Oh man, MrExcel.
That's quite a graphic and now I'm feeling down but, yeah, you know, I don't know how to do that to 3 different tables like that.
If we were to do this LOOKUP, you can look up 3 different values.
So, the lookup array right here, the second incarnation of LOOKUP, you can give it 3 values, right?
It's expecting 1 value.
We give it 3.
So, that means LOOKUP will spit out 3 values but the table has to be one table.
So, I think the original question said 3 different categories, right?
So, F4, ) if we highlight this and F9, you'll see that it spits out 3 different values, and then, if we're multiplying it when we put inside the product function, this will require CONTROL+SHIFT+ENTER because PRODUCT, that NUMBER argument there is not programmed to do array calculations without CONTROL+SHIFT+ENTER.
So, there, it looked up 3 values and multiplied them but they all came from the same table.
Maybe someone else knows a formula for this particular example that would be more efficient than 3 different LOOKUPs or VLOOKUPs.
Alright.
Throw it back to MrExcel.
Bill: Okay.
So, Mike, let me test something here.
Let's say we just have some function, equal function, that's expecting 2 values, expecting A and B, is it true to say that if that function would accept an array, that it would be possible to put an array for either A or for B?
For example, a 3 position, or 3 cell range here or a 3 cell range here, and force us to pop out 3 answers, but it would be impossible to ever pass a 3 value array for A and also a 3 value array for B because, well, frankly, the function just wouldn't know what to do.
Does it use this item with this item, this item with that item, and the 3 with a 3, or does it produce 9 results, and so the question for you, as the author of CONTROL+SHIFT+ENTER, is is it generally true that if we have a function that accepts some number of arguments, you might be lucky enough to be able to put an array in place of one of those arguments but never would you be able to put an array in for 2, 3, or 4 of those arguments where it's expecting a single value, and while I was driving back from Pennsylvania today, I started to think about this graphic that I put up this morning, and said, okay, even if Excel would never be able to do this, we could certainly write a function in VBA that would be able to do that, where we pass the 3 values, B, C, and D, and then Table 1, Table 2, Table 3.
Here's the code that I have for that.
ALT+F11.
So, not efficient code at all, certainly, but it works.
It actually goes through and programmed exactly kind of what I dreamt up overnight with…I was being facetious this morning when I said it because I knew it would never work, but, actually, with some VBA, it would work.
Interesting, though, to figure out if it's generally true that you have just any Excel function that's going to accept an array in place of a single value, you at best will be able to put it into 1 of the arguments and not 2, 3, or 4 of the arguments.
Mike: Yeah.
This is a great concept to have although, you know, the truth about Excel, as we all know, is if we say, yeah, this probably doesn't happen, then of course it will somewhere.
Most of the time, this is good.
So, for example, if you come up here and highlight that whole LOOKUP, that argument expects 1 value.
We give it multiple values.
So, when we evaluate it, it spits out multiple values.
F9.
That’s the definition of a function argument array operation.
That function argument is expecting a single value, we give it 3, so LOOKUP will spit out 3 values.
If we would try to apply this right here to our LOOKUP formula, right, that hypothetical thing, so I say, hey, LOOKUP, look up those 3 values, and then we try to give it 3 different tables inside of (), you know, it has no idea what to do with that.
However, there probably are some examples of a function that can take 2 arrays.
Now, arrays could be actual…just multiple values you give it or an array operation that spits out multiple values.
An example of a function that could take 2 would be the SUMIFS.
So, here we have our criteria range.
We gave it 4…we gave it the blue column to add, our orange criteria range is REGION, and then, there for criteria 1, we gave it 2 values, and then we did the same thing for criteria range 2.
We gave it all the SALES REP and then we gave it 2 here.
So, when we highlight the SUMIFS, we gave it 2 in each of the criteria argument, so when I hit F9, it spits out 2.
It’s not going to spit out 4.
It'll spit out 2.
Now, in this particular circumstance, we would just…we had a database, use DSUM, right, AND criteria.
It’s actually AND and OR.
It’s JO and WEST or MO and EAST.
So, obviously, we DSUM, but you could imagine a different situation where you can't or DSUM is hard to use when you want to copy the formula.
So, here’s an example with a third criteria, this PRODUCT here.
So, we're still using the criteria from up here where we're giving it a function argument array operation right there.
If we were to highlight the SUMIFS and hit the F9, it's spitting out 2 values.
It’s given us a 2 and a 0, yeah, because it's…well, this…so JO WEST and PRODUCT 2 or MO EAST and PRODUCT 2, so 2 and a 0.
So, if we wanted to copy it down with this criteria, then SUMIFS might be the trick.
Now, if we think about this, if it's…we're given multiple arguments in a function, multiple items, there is a way to do that for some functions.
So, for example, the LARGE function, SMALL function, FREQUENCY, RANK, PERCENTILE, all these functions in the array argument, you can actually give it multiple ranges.
Now, this, you know, I guess it is an array of items.
We are giving it 2 different tables here.
You can see but, by putting them inside a parenthesis, it's just going to take all of these as 1 group and numbers, and then we'll make an array function, function argument array operation in the case, say, hey, give me the first and second biggest from these 2.
If you look through the values, it better be 7 and 9.
So, if I were to highlight the large, because that argument right there has 2 items, F9, the large will spit out 2 items.
Now, actually, I've been meaning to do a video on which functions can accept multiple ranges inside of percentage…parenthesis and I have a video cued up, but I'm quite busy.
I'll have to do it sometime soon because it's a pretty exciting topic.
You know we try to do that as we originally proposed and that just is not going to work.
So, alright.
Throw it back to MrExcel.
Bill: Well, if there's anyone still here twelve and a half minutes in, you're saying this took a long time, but, hey, for Mike and I, this took over 15 hours, 7 parts back and forth.
When I started out this morning, I said this is a pretty easy question.
There is no way that this is going to go on for a long time, but it was one of our longest duels.
Long and winding, hopefully interesting to you, at least if you're still here.
So, I want to thank you for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 141, multiplication factors based on goals met.
Alright. Today's question is sent in from Gareth at YouTube.
3 categories.
So, for example, category 1, if I scored 0 goals against that I get a multiplication factor of 0.5, 1 goal multiplication factor of 1, 2 goals multiplication factor of 2, and there's 2 other categories but with different multiplication factors.
Alright.
So, I imagine it's something like this.
Category 1, and this is the one he spelled out, if you meet 2 goals, multiplication factor of 2, 1 goal 1, 0 goals 0.5.
So, I created 2 more tables for category 2.
Number of goals, different multiplication factors.
Category 3, number of goals, different multiplication factors, and then, you know, so, he has a table of scores that fall into different categories, but how do I put that into a formula, alright?
So, you know, maybe we have people over here.
It's, like, it's some sort of an employee review system and so on, and people meet various goals or don't meet goals, and how do we score that?
Alright.
So, I suppose we could go really, really fancy, but, the way I see it, we have 3 factors that we're going to multiply by each other.
I can just do that in a single formula here.
So, =VLOOKUP of category 1 in this table, the green table over here, , 2 because I want the second column, and to simplify to change the FALSE to a 0.
FALSE and 0 are the same.
VLOOKUP of the second category, that's 5 here in C12, into this table, the brown table, , 2 , 0 and then * D12, the red table, , 2 , 0 and copy that down.
It does the multiplication and we can probably stack rank these employees based on the number of goals met.
[ =VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0) ] Just kind of old-school, 3 different VLOOKUPs, multiply the VLOOKUPs, nothing fancy here.
Mike, let’s see what you have.
Mike: Thanks, MrExcel.
Hey, I don't really know a different way besides doing 3 LOOKUP functions.
I don't know how to, like, do some array formula lookup, those 3 things all at once.
So, the only thing I can think of to do is switch the lookup function.
Same 3 tables, same everything.
I'm going to notice that since these are goals, we're probably always starting at smallest and going to biggest, and the tables all seem to be taller than they are wider.
That means I can use the LOOKUP function, not VLOOKUP.
LOOKUP only does approximate math.
That's why this needs to be sorted.
It also only does VLOOKUP when the table is the same size or taller than it is wide.
So, I'm going to LOOKUP this, , and then the LOOKUP VECTOR.
Actually, this is the array down here.
I'm simply going to highlight the table and LOOKUP always takes from the last column, so you don't need to put a column number.
I'll hit F4, and then I’ll *, LOOKUP the next one within this table, F4, ). Don’t need to put a column number.
It always takes from the last column, and then LOOKUP this last number right here, , within this third table, F4, ). So, 3 LOOKUPs instead of 3 VLOOKUPs, and then copy it down.
Alright.
Throw it back to MrExcel.
[ =LOOKUP(B12,$J$2:$K$4)*LOOKUP(C12,$J$7:$K$12)*LOOKUP(D12,$J$15:$K$17) ] Bill: Oh Mike.
Come on.
Come on.
You know a lot of us were dreaming that you were going to knock out some sort of SUMPRODUCT with a LOOKUP of the ARRAY of 3 different LOOKUP values into 3 different LOOKUP tables and CONTROL+SHIFT super ENTER and it would all happen, but, of course, that just is fiction.
That would never work.
So… Mike: Oh man, MrExcel.
That's quite a graphic and now I'm feeling down but, yeah, you know, I don't know how to do that to 3 different tables like that.
If we were to do this LOOKUP, you can look up 3 different values.
So, the lookup array right here, the second incarnation of LOOKUP, you can give it 3 values, right?
It's expecting 1 value.
We give it 3.
So, that means LOOKUP will spit out 3 values but the table has to be one table.
So, I think the original question said 3 different categories, right?
So, F4, ) if we highlight this and F9, you'll see that it spits out 3 different values, and then, if we're multiplying it when we put inside the product function, this will require CONTROL+SHIFT+ENTER because PRODUCT, that NUMBER argument there is not programmed to do array calculations without CONTROL+SHIFT+ENTER.
So, there, it looked up 3 values and multiplied them but they all came from the same table.
Maybe someone else knows a formula for this particular example that would be more efficient than 3 different LOOKUPs or VLOOKUPs.
Alright.
Throw it back to MrExcel.
Bill: Okay.
So, Mike, let me test something here.
Let's say we just have some function, equal function, that's expecting 2 values, expecting A and B, is it true to say that if that function would accept an array, that it would be possible to put an array for either A or for B?
For example, a 3 position, or 3 cell range here or a 3 cell range here, and force us to pop out 3 answers, but it would be impossible to ever pass a 3 value array for A and also a 3 value array for B because, well, frankly, the function just wouldn't know what to do.
Does it use this item with this item, this item with that item, and the 3 with a 3, or does it produce 9 results, and so the question for you, as the author of CONTROL+SHIFT+ENTER, is is it generally true that if we have a function that accepts some number of arguments, you might be lucky enough to be able to put an array in place of one of those arguments but never would you be able to put an array in for 2, 3, or 4 of those arguments where it's expecting a single value, and while I was driving back from Pennsylvania today, I started to think about this graphic that I put up this morning, and said, okay, even if Excel would never be able to do this, we could certainly write a function in VBA that would be able to do that, where we pass the 3 values, B, C, and D, and then Table 1, Table 2, Table 3.
Here's the code that I have for that.
ALT+F11.
So, not efficient code at all, certainly, but it works.
It actually goes through and programmed exactly kind of what I dreamt up overnight with…I was being facetious this morning when I said it because I knew it would never work, but, actually, with some VBA, it would work.
Interesting, though, to figure out if it's generally true that you have just any Excel function that's going to accept an array in place of a single value, you at best will be able to put it into 1 of the arguments and not 2, 3, or 4 of the arguments.
Mike: Yeah.
This is a great concept to have although, you know, the truth about Excel, as we all know, is if we say, yeah, this probably doesn't happen, then of course it will somewhere.
Most of the time, this is good.
So, for example, if you come up here and highlight that whole LOOKUP, that argument expects 1 value.
We give it multiple values.
So, when we evaluate it, it spits out multiple values.
F9.
That’s the definition of a function argument array operation.
That function argument is expecting a single value, we give it 3, so LOOKUP will spit out 3 values.
If we would try to apply this right here to our LOOKUP formula, right, that hypothetical thing, so I say, hey, LOOKUP, look up those 3 values, and then we try to give it 3 different tables inside of (), you know, it has no idea what to do with that.
However, there probably are some examples of a function that can take 2 arrays.
Now, arrays could be actual…just multiple values you give it or an array operation that spits out multiple values.
An example of a function that could take 2 would be the SUMIFS.
So, here we have our criteria range.
We gave it 4…we gave it the blue column to add, our orange criteria range is REGION, and then, there for criteria 1, we gave it 2 values, and then we did the same thing for criteria range 2.
We gave it all the SALES REP and then we gave it 2 here.
So, when we highlight the SUMIFS, we gave it 2 in each of the criteria argument, so when I hit F9, it spits out 2.
It’s not going to spit out 4.
It'll spit out 2.
Now, in this particular circumstance, we would just…we had a database, use DSUM, right, AND criteria.
It’s actually AND and OR.
It’s JO and WEST or MO and EAST.
So, obviously, we DSUM, but you could imagine a different situation where you can't or DSUM is hard to use when you want to copy the formula.
So, here’s an example with a third criteria, this PRODUCT here.
So, we're still using the criteria from up here where we're giving it a function argument array operation right there.
If we were to highlight the SUMIFS and hit the F9, it's spitting out 2 values.
It’s given us a 2 and a 0, yeah, because it's…well, this…so JO WEST and PRODUCT 2 or MO EAST and PRODUCT 2, so 2 and a 0.
So, if we wanted to copy it down with this criteria, then SUMIFS might be the trick.
Now, if we think about this, if it's…we're given multiple arguments in a function, multiple items, there is a way to do that for some functions.
So, for example, the LARGE function, SMALL function, FREQUENCY, RANK, PERCENTILE, all these functions in the array argument, you can actually give it multiple ranges.
Now, this, you know, I guess it is an array of items.
We are giving it 2 different tables here.
You can see but, by putting them inside a parenthesis, it's just going to take all of these as 1 group and numbers, and then we'll make an array function, function argument array operation in the case, say, hey, give me the first and second biggest from these 2.
If you look through the values, it better be 7 and 9.
So, if I were to highlight the large, because that argument right there has 2 items, F9, the large will spit out 2 items.
Now, actually, I've been meaning to do a video on which functions can accept multiple ranges inside of percentage…parenthesis and I have a video cued up, but I'm quite busy.
I'll have to do it sometime soon because it's a pretty exciting topic.
You know we try to do that as we originally proposed and that just is not going to work.
So, alright.
Throw it back to MrExcel.
Bill: Well, if there's anyone still here twelve and a half minutes in, you're saying this took a long time, but, hey, for Mike and I, this took over 15 hours, 7 parts back and forth.
When I started out this morning, I said this is a pretty easy question.
There is no way that this is going to go on for a long time, but it was one of our longest duels.
Long and winding, hopefully interesting to you, at least if you're still here.
So, I want to thank you for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
