Tabulating totals with the VLOOKUP function and array constants

- Excel's VLOOKUP functions is one of the most powerful and widely used functions in Excel. It's also considered one of the more important ones, too. At least one Excel author has made the statement maybe provocative, it's the most important function in Excel. Well whether it is or isn't, we want to use it here, but perhaps in a way you have not seen. We've got a big list here, and it's going to grow and grow and grow. These are salespersons in different offices around the country. They sell, imagine, property so they don't have that many sales every year. And from time to time, we want to check on this person or that person. We might eventually have a pick list, maybe in column J. And have a list of names, pick one out of there as we're looking for different data about that person. So I'll just copy a name over there, here's Walter White in cell A10, I'll just drag that over with the control key, put it right there, let go of the mouse first. Alright, simple VLOOKUP, many of you would be familiar with this. We simply want to look up his name, comma, in the list starting in column A. Now, maybe we're trying to get here initially, just the data for 2017. So, we start in column A, but we want to drag all the way into that column G that contains the 2017 data. Comma. Which column are we looking for that has the data that we want? It's in column G. But reading from column A on the left, it's the seventh column. So we put in the number seven, comma. And the match for Walter White has to be exact, it can't be close. And we put in the word false. You can double click it in, or you can use zero, either one. Either one means exact match. We press enter, fifty-seven as we highlight this and look upward here, that's the entry we want right there in G10. Let's keep this highlighted. We'll add a color to it. There we go, yellow. Okay. Now, turns out from time to time what we really want out of here maybe would be a total across, for all these years for one of these purses or maybe just the last three. Or maybe an average here. Or maybe an average of the last three. A variety of different entries, maybe even a median, who knows. So let's rethink this a little bit. What we'd like to be able to do here, combined with the sum function, is to say let's pick up data not just from column seven, seventh column over, but the fourth, the fifth, sixth, seventh, and eighth column, maybe for all of these years. So let's revisit this a bit. VLOOKUP, we're looking for Walter White, for the moment, we've got to copy that in J2. Instead of looking just in columns A through G, We want to be looking in columns A through H. And then not just in the seventh column, but, and here's where things are going to be a little bit different then what you might have seen in the past, we use a curly bracket if we want to put in an array constant, a series of numbers. This symbol usually is one key to the right of the letter P, using the shift key. Curly bracket, and now, one by one, column four, comma five, comma six, comma seven, comma eight. And a right curly bracket there, comma zero. We want this to be an exact match. After getting the data from all these, we want to tabulate this by way of a sum. So let's put a sum in front of this. Now, in many versions of Excel, and perhaps the one you're using now, you cannot simply press enter here and expect an answer that's correct. If you do have the latest Office 365 upgrades though, the way Excel handles this kind of information is with a simple enter. Earlier versions you would've had to have used what's called an array formula, and you would need to press, and you still might need to press control + shift + enter. For the example here I'll press enter and there's a number, and it looks right but let's highlight these numbers over here, look at the bottom of the screen, there's the total three three three. Now, just as easily, we could use this, and let me copy Walter White's name down again here to make this a little bit simpler. I'll copy this formula downward, and simply change this to be an average. Or of course a median, or other functions as well it could be used here. And again, enter. The average of those, 66.6, once again we can highlight these numbers, look at the bottom of the screen in our status bar and see those. And for reference, this is not truly necessary, we could use a function here in both of these cells called formula text, it simply displays the formula in any cell that we wish it too. And in this case, I'll simply use it on this cell and control + enter since they're both highlighted. That's a reminder as to what's going on here. That's the way that the functions are looking here. And as I suggested, we could be doing this with median and others. But do begin to recognize that the way Excel is using array formulas is changing and only if you have the latest upgrades, can you enter the formula like this and press enter. Earlier versions, press control + shift + enter. But the ability to pull in data from multiple locations and then do a sum here could be a more powerful use of VLOOKUP than you've seen already.