Excel Formulas & Functions Part 5: Lookup & Reference Functions | Chris Dutton | Skillshare

Excel Formulas & Functions Part 5: Lookup & Reference Functions

Chris Dutton, Founder, Excel Maven

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
16 Lessons (1h 8m)
    • 1. Introduction To Lookup & Reference Functions

      1:23
    • 2. Working with Named Arrays

      3:30
    • 3. ROW/ROWS & COLUMN/COLUMNS

      3:18
    • 4. VLOOKUP/HLOOKUP

      5:54
    • 5. Joining Data with VLOOKUP

      6:23
    • 6. Fixing Errors with IFERROR & VLOOKUP

      4:30
    • 7. VLOOKUP Reference Array Options

      6:18
    • 8. The INDEX Function

      1:59
    • 9. The MATCH Function

      2:32
    • 10. Using INDEX & MATCH Together

      6:08
    • 11. Combining MATCH with VLOOKUP

      4:47
    • 12. UPDATE: VLOOKUP Correction

      5:05
    • 13. The OFFSET Function

      2:05
    • 14. Combining OFFSET with COUNTA

      2:58
    • 15. PROJECT SHOWCASE: Using OFFSET to Create a Dynamic Scrolling Chart

      9:51
    • 16. HOMEWORK: Lookup/Reference Functions

      0:55

About This Class

This course is part 5 of a 9-part series on Excel Formulas & Functions: from basic to advanced.

Lookup & reference functions are some of Excel's most versatile tools for joining and analyzing data. In this section we'll dive into VLOOKUP and HLOOKUP formulas, explore the powerful INDEX/MATCH combo, and introduce advanced demos using OFFSET and COUNTA to generate dynamic ranges.

About the Excel Formulas & Functions Series:

In this series, you will develop tools to transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Courses cover 75+ formulas, and feature hands-on, contextual demos and practice exercises designed to help you not only memorize formula syntax, but to think like Excel.

You'll learn how to write complex, powerful functions from scratch, allowing you to:

  • Build dynamic tools & dashboards to filter, display and analyze your data
  • Join datasets from multiple sources in seconds with LOOKUP, INDEX & MATCH functions
  • Pull real-time data from APIs directly into Excel using WEBSERVICE & FILTERXML
  • Manipulate dates, times, text, and arrays with ease
  • Automate tedious and time-consuming analytics tasks (no VBA required!)
  • And much more

If you're looking for the ONE series covering all of the advanced formulas and functions that you need to become an absolute Excel rock star, you've found it!

Transcripts

1. Introduction To Lookup & Reference Functions: All right. Welcome to Section five. Look up reference functions. Now we're starting to get into some of the really, really powerful heavy hitting formulas s. Oh, I can't wait to get started in this section. We gonna talk about named a race, which is a way to more efficiently work with your look up formulas In cases that you're using the same arrays over and over. I will talk V, look up in age, look up, Which are kind of two classic look up functions that are used all the time and excel. We're gonna talk about some formulas like roe and rows columns, columns, which could be really interesting components. As part of other look up functions will go into index and match talk about how to combine those how to use match with a V look up function to do some really powerful stuff. And then we'll talk offset, which is a somewhat complicated but really, really interesting formula and will do in Excel demo during that lecture where we're gonna build this dynamic chart that can scroll and zoom and move, basically do some stuff that most people would never think excels, capable of some excited to show you that we've got to files for download. Got the PdF Excel for analysts, look up Reference Functions and the Excel File, Section five Look up reference. So go ahead and download both of those and let's get ready to roll. 2. Working with Named Arrays: So I want to take just a second to talk about something called named a Raise before we dive into specific look up functions. Named arrays are really nice tool to help you simplify your look up functions, especially if you find yourself using the same exact data array in multiple places or across multiple formulas. So, for example, if I have product level data in cells a 1 36 and I'm using this exact date a reference in a number of places, I could give it a name. In this case, I'm calling it a peril so that in the future, when I write formulas that refer to this array, I can either write a one Colin D six or I can just right apparel, which is the name that I've assigned to this array. So excel. Recognize that and understand that I'm talking about the array from a 1 36 Um, so let's jump into Excel and take a look at the file that we're gonna be working with. Section five. Look up reference. We've got four tabs here. First tab called Hitting Data, is one of our raw data tabs. We've got player names, years on, and then a bunch of hitting data, metrics and columns see through end fielding data. We also have a player in your column and then some additional fielding data metrics and C through G. We've got a price checker tab, which we're gonna be populating once we learned how to use index and match and combine the two and then we've got scroll chart, which is gonna be a really awesome tool that we're gonna build using the offset function on . We're gonna create a chart that could be scroll herbal and zoo mobile and dynamic and basically do stuff that most people would never think excel is capable of. So really excited to share that, um, but jumping back to the concept of named arrays let's show an example. Let's go to the fielding data tab. If I want to use this entire array on this tab for something like a look up function, what I can do is click on a one hit control shift arrow, right Arab down so that I've got the entire array selected in the first way to define this as a named Array is to click on this box here, which is the name box and just give it a name. I'm gonna call it Fielding. And now, if you go into the formulas tab, click the name manager. You'll see that it's created a new named Array called Fielding that refers to the fielding data tab A 1/3 G 12,056 which is exactly what I just selected. I'm going to show you one other way to do that. So I'm gonna delete this. Yes, close this box. And now, instead, the other option is to start in the formulas tab, click on this defined name button, give it a name or call it fielding again. I can add a comment if I want. This is feeling data and then click the Data Selector button and then select that exact same array that I just did. Press the button again and lock it in. So if I go back to the name manager, you can see the same exact thing happen. It created a new name DeRay called Fielding. That refers to a 1/3 G 12 56 on the fielding data tab. Um, so there you go. That's how you create named race 3. ROW/ROWS & COLUMN/COLUMNS: all right. I want to start with some of the simpler look up reference functions. Eso We're gonna talk ro ros column columns, which typically aren't actually used on their own. They're typically used as components of larger, more complicated formulas, but they do serve a very interesting and in some cases, very useful purpose. The row function simply returns the road number of a given reference. So if I say what's the row of a five, it would return five because sell a five lives in the fifth row. Similarly, the plural ized version Rose just tells me the number of total rose within a given array. Um, this third example down here, it uses a user defined array, which we'll talk about in, I believe, Section nine, the array formula section, um, Colin and columns essentially do the exact same thing. They just returned the column number of a given reference or the total number of columns within s any given array. Um, one additional little tip here. If you leave the reference out and just right row with an open close parentheses or column open close parentheses, it will return the row or column number of the cell in which the formula is written. So let's jump to excel and take a look at that. I got a blank workbook here just to give me some data. I'm gonna do a quick rand between 1 to 100. Apply that down and over. So I got a chunk of data can hit. Copy, Do my Ault H V V Shortcut. And now I've got an array of random data from 1 to 100 between cells, a one and C 12. So let's play with Roe and columns functions a little bit. I'm in E one right now and type equals row. Open, close, enter. That's one because I typed it in the first row. And as I apply this down, you'll see how that just updates to return Whichever road that the formula is currently residing in, uh, what else to do A column formula here. Open, close, enter and dry it to the right and you'll see the same thing. It just goes up by one each time. So that's row and column with no reference. If I wanted to test the row or column number of a specific reference, I could do that so I could say What's Thea Row of de six returned a six or could say, What's the column? Groups of, Ah, a three return one because it's in column A on then last but not least, I could do equals Rose of this entire array. Here, you returned 12 because it's 12 rows tall or I could do columns of the entire array. It will return a three since its three columns wide. Eso again. These aren't typically used on their own, as you could see there kind of simplistic functions, but as components to more interesting formulas, they conserve a really valuable purpose. So there you go. Ro Ros column columns. 4. VLOOKUP/HLOOKUP: okay. I want to start by talking about two of the most common yet powerful look up functions in Excel. We look up and h look up. These air generally used when you need to combine two different data raise with a common field. So, for instance, if I have product level data in one array where I have quantity and product, I d. And then have another array of data with the same products and the price information, and I want to merge those data sources so that I have quantity I D and Price all in one spreadsheet. The look up is a great tool to use to do that. So there are a few components of the look up function. It starts with a look up value. So this is the value that you're trying to match its typically be common field that exists between the two erase the source and the look up array. The second component is the look up array. So this is where you're trying to find that look up value. And the third piece is the column index number. So in your table array, where you're looking for your look up value Which column of data would you like to return was, That's typically where your variable is that you're trying to pull into your first source a right and then last but not least, you have a range. Look up, Um, which basically just says they're trying to match the exact look up value or something similar. You're trying to look up the exact value which you'll use for text, strings and words. You'll put a zero or the word false there. Otherwise you can put a one. For instance, if you're looking up a number with a bunch of decimal places and you don't need it to match , exactly, you could use that. But just so you know, 99% of the time, you're going to use a zero or exact as you arrange, look up. So in this example here again, I have this product level data that it just described and in one array have got product name, quantity and product I D. And in my second array, I've got product and price. So as you can see, product is the common field between them, and what I want to do is find the price in my look up array and pull it into my source. All right, so in cell D to have created a price column, I'm going to say V look up. Whatever's in a two, which happens to be the product T shirt within my table array G one through h five. So it's gonna go row by row until it finds the look up value, which in this case, it's finding it in Row five. And then it wants to return the data from the second column over. Since that's the column in next number So it finds T shirt moves to the second column over , finds the value of 14 99 and returns it in Cell D to. And then, as you apply this look up table down, it's gonna be looking up sweater than shorts than socks all within this look up array. So that's Velicka H Look up works almost exactly the same way. It's really the same syntax. The only difference is that you need to use an H Look up. If you're look up, Array is transposed, which is kind of an odd way of showing the data, but sometimes you'll run across data sets that are built in this way, so it works the same way. The only difference is that it will move column by column until it finds its look up value and then move down to a specified row in next number, as opposed to moving row by row and moving to a column and next number for the V. Look up. So again, same exact concept. You just use it when your data is transposed. So two very, very important rules that you need to remember when you use V look up or h look up functions. The first is that your look up value has to be in the first column. If you're using a V look up or the first row. If you're using an H, look up of your table, a rain so you can't have a table array that goes from column eight D, where your look up values or a matching column is calling Be. Excel just doesn't like that. The second rule is that Excel will always return the value from the top most row. If you're doing a B look up or the left. Most column. If you're doing an age look up of a table array if multiple instances of your look up value are present. So this is a very, very important distinction to make here, which is that, uh, if I go back to my V, look up example. For instance, on I'm looking at the Value T shirt in this look up array, let's say two lines in a row had T shirt as the product with different prices. The V look up is going to stop the first time it finds the value is looking for in this case T shirt and return the price in the second column over for that first instance, it's not even going to know that there were multiple instances because it stops at the 1st 1 So the way to get around that is to identify a key that's common to both data sense and unique for every row. That's the key piece here, so it typically takes the form of a contamination of multiple fields. We'll talk more about those specifics in the text function section, but basically can Cat Nation, which can be accomplished just using the ampersand sign and excel, just mashes together to text fields into one um so we'll see in our baseball example that we've got players and years and we're going to create a common key based on both of those to properly use the V look up function. So that's if you look up and h look up in the next lecture. We're gonna talk about actually putting this into practice by merging our data. 5. Joining Data with VLOOKUP: all right. Enough talking about V. Look up in hte. Look up. Let's actually use it. Um, it's really hard to really learn these formulas until you practice them on real data. So let's go ahead and get some hands on practice using the V Look up function Open up the section five. Look up Reference Excel Duck and will work out of there to recap Got my hitting data tab, which is organized by player by year with a number of hitting metrics from column C through end and then similarly have a feeling data tab by player by year, with additional fielding metrics here, the goal of this exercise is going to be to pull in those fielding data metrics into columns. Oh, through s in my hitting data tab. So this is gonna be like my source or master data tab that joins those two sets of metrics together in one place. So what I can't do is just start writing of you. Look up function on the player named, for instance, as my look up value because I have the data by year as well. So if I looked at the player name in the fielding data array. It's gonna find the first instance of that player, you know, if you played in 2010 for instance, but ignore any later instances. So if I have a player who exists in the data set five times because he played all five years of the data, then that V look up function based on Leon Player name is going to miss four of those instances it's gonna find the 1st 1 is going to return the associated values and ignore the others. So the first step that we need to do is create a key column. So in hitting data in a right click and insert a column to the left of column, a call it key and to define this function or this new dimension destroyed equals B two ampersand C two. This is called a Can Katyn eight function, and it just mashes those text strings together to create a new, unique field. So there's two things that's important here, one its unique. There's only one instance of each of these values in this entire column, and to it's in the first column column A. That's very important. If you remember the first rule of the look up functions. So I'm gonna jump to feeling data and do the exact same thing. New column. A college key. It's gonna be equal to be to ampersand C two quiet down. And now I've got my unique common key between both tabs that I can use from I look up So the next step that I like to do is just say our it Let's jump to my look up data and find which fields or which variables I'm looking to pull into my master sheet. And it's gonna be these five. So I'm just gonna pace them. Here is placeholders and this is where all my view look up functions are gonna live. So let's start with the position. Uh, Colin P I'm just going to start writing. Every look up so equals V. Look up, Open the parenthesis. I look up value is going to be whatever my key is here. So a to I look up table array, Where am I trying to find this player? Key name is in the fielding data from a one all the way over and all the way down to H 12,000 and 56 before I do anything else? I'm gonna press F four, which fixes that entire array in place so that I can apply this look up formula down. Once I've written it and not have this data array shift along with it, it's not a press comma column index number. It's basically the column where my position feel lives, because that's the metric that I'm trying to populate right now, which is in column 1234 So column index four and then I'm just gonna do zero for exact match. Close the parenthesis hit. Enter. So now if I apply this all the way down, as you can see, it's looking up each player name. If I press f to dive in yet, the table array or look up array is not shifting. So there you go. That's position now. One other little trick that I use to save myself some time. You'll notice if I just try to drag this formula to the right, I'm gonna get an error. And the reason is if you look at how the formula changes, watch the look up value first. It's a to it shifts to be, too, because that was a completely relative array. So I moved it to one column to the right. A change to would be. But in this case, no matter which column I'm trying to pull in with a look up function. I'm always gonna be looking up the same key field. So the A is never gonna change from in a So what I'm gonna do is press F 43 times to just fix the column, but leave the row relative That way I can apply this down again. The Rose will left eight, which is what I want. But now if I shifted over, it stays a two, which is what I want. Now you'll notice it's actually the same exact function because nothing is changing for my references. At this point, all I need to do is change the column index number because remember, position was pulling in the value in the fourth column put out is in the fifth Column. So I just need to change the 4 to 5. And there you go, comply that down. Same thing. I could drag this over. Change the five to a six to pull in assists, which live in the sixth column over And then I'll do the same thing for errors and double plays, which live in columns seven and eight, respectively. And then grab all three of those. Double click that corner to apply them down. So there you go. I've written a look up function to pull in my fielding data into one master source tab. 6. Fixing Errors with IFERROR & VLOOKUP: Okay, so I've showed you how to use an if error statement back in the logical operator section. I want to revisit that and show you how it works the exact same way. No matter what formula using in this case, we're gonna apply it to be look up function we've already written. But it's important to revisit it also, because it becomes more and more relevant and useful as you start applying it to look up in reference functions specifically. For instance, if you don't have an exact 1 to 1 match between your data and your source table and your look up table, that's gonna drive an N a value on and in this case, with the data that we're actually using, I know for a fact that we're gonna drive errors because there are players who show up in the hitting data tab but will not show up in the fielding data tab because they only hit, for instance. So when the V look up function tries to find that player in the look up array, which is in our fielding date attempt, it's gonna come up empty handed and return in N A. So let's see if that's actually happening, first thing I need to do is just apply filters out to the rest of the columns that we just created. Select row one going to the data tab uncheck filter, and then check it again. That will just reapply the filters all the way out to Conte. And now, if I look at any of the columns were written these we look up functions. So P Q R, S t I'll just use the drop down and see that there are, in fact, en a errors being populated. So what I'm gonna do, just like I did in the last section, is I'm going to go into the formula bar, click right after the equals and right and if error statement. So if error open, the parenthesis value is gonna be the entire function I just wrote jumped the end press comma. And in this case for the position field, since the player doesn't really have a position, if he's only in the hitting data set, I'm just gonna call it other for now. Close it off, press enter and then apply that formula down. And now when I re filter, you can see that the has changed to another value not for the actual statistics and columns Q, R, S and T. This is where the affair statement takes on an even more important role. So right now I'm getting any values in all of these columns. And the problem with that is that if I try to you select the entire column to see the sun excels getting thrown off because it's seeing thes error messages and it's it's preventing excel from summing or doing any other statistical operation on the legitimate values. So you can compare when you select. You know, a data column like M or N, where you're seeing average count, and some compared to our calculated fields, which you're only showing counts because they have these errors built in there throwing them off. So what I'm gonna do is again, I'm gonna write in a fair statement, jump to the end, and I'm just gonna take zero. I'm not going to use quote, so that's gonna input the actual numerical value of zero in cases where you in there. Then, when I apply it down, select the entire column you could see now excels, able to calculate an average count and a sum which enables me to do any kind of statistical operation that I want, just like it's any other numerical fields so I can pull it into a pivot table. I can run stats, functions on it, whatever I want. So I'm gonna do the same exact thing for the next three columns. There are two ways to do this. I can just add if errors to each one, which I'm doing now, or it can add the affair to this one, apply it over and then change the column. Index number 27 and two and eight, just like we did only first created the function. It's totally up to you, whichever you're more comfortable with. But both approaches work. So now, as you can see, if I select column R s or tea, I'm getting the average count in some. And if I look down in the filter that any value has disappeared and been replaced by zero so again, if they're really, really important, especially when it comes to look up functions 7. VLOOKUP Reference Array Options: All right. So you've got a beautiful V look up formula written. It's populating data properly. We've even wrapped it in a fair statement to account for the missing values. All is good. I want to talk about one other tweak that we can make to the table array, Um, and two other approaches that we can use which can be beneficial in certain situations. So right now we've written our look up with a fixed, uh, table array. So right now, the look up is looking for the look up value within the array from a one through H 12,056 exactly that array. There are two other approaches we could take. The first is to use at named Array. So if you recall Ah, in one of the early lectures in this section, we created a named Array based on our fielding data. But if you recall when we created it, we hadn't defined this key column a yet. Um, so if you actually select be one the recall him h and then control shift down. There's my fielding array. So it basically shifted my named array over, Um, and in this case, my look up. Array needs to include column A because that's where my look up values gonna live. So what I can do is going to the formulas Tab, Select name Manager. Here's my fielding array that we had created. Gonna edit that. All I'm gonna do is change the B one. Delete the be changing to a hit. Okay, when I closed that now, if I select a one control shift ever right arrow down, you can see that it's defined as my fielding named Array. So now that we've made that little tweak, I can go back to the look up and just jump into the table or a piece of my formula, delete the entire I think, and just right fielding. And as you can see, it will apply all of the same values. I could do the same thing here again. I'm gonna delete the entire table array, starting with the tab name fielding data followed by the exclamation point to leave all of that and just right fielding. And again, Excel will recognize that as a named reference, and it will know exactly what reference that refers to and populate the correct errors. So I'm just going through columns. Q R S T. And just making the same update here in every case, note that I'm not putting fielding and quotes or anything because it's not a text string. It's a reference to a named Array and, as you can see, X Ellis popping up this little box because as I start typing, fielding its searching through the name manager and seeing if I've created any named raise with that name. And so, in fact, it does find it's something you just give it. A click will drop it in, and we're good to go so I can go ahead and apply all those changes I just made down. And once this finished calculating, you'll see that it's fielding in all cases so you can see it's a little bit more compact, a little bit of a cleaner formula. The problem that I have with named a raise is that a I need to remember that have created this name for it and use the name manager to check exactly which sells That named Array refers to. The second is that it's it's fixed. So if I need to modify or add data, I'm gonna need to edit the fielding reference to account for that. So one last option for the table array, which I actually prefer and I use quite a bit, is rather than using and named unnamed array. And rather than using a fixed, specific array like a one through H, whatever it waas, what I'm gonna do is select fielding data. Note that I'm in the table or a piece of my formula right now. Select fielding data. Just click. All of column may hold down shift and all of column H and hit enter. Apply that down. So same exact result You can see now it's looking up. Basically any rows and columns A through h not just down to 12,056. And the benefit of doing this is that Now, if I get a new additional year of data or if I refresh the data in my fielding data tab, I won't have to touch those v lookups. I want to touch the reference and I want to touch the name manager because every time it performs the look up, it's gonna search every populated row from Column A through H s. So it's a it's a more effective more efficient way to use. Look up functions when you have a dynamically constantly changing or updating look up reference table. So that's my little tip. I use it quite a bit. I'll just show you the same exact thing for the last four columns just so that you're comfortable with it a through H enter. And again, this just seems to save me quite a bit of time. Eso that I don't have to remember. You know, all these nuances about how, if defined, certain named a raise or exactly what? My look up table, uh, is currently defined as in terms of the cell reference. I just always know that no matter what, my lookups gonna work properly. So supply those down, you know, take a minute to calculate. And there you go. So you see that nothing's changed on the surface because all three methods of writing this formula that have shown you do the exact same thing, it's just that they all have very specific benefits and drawbacks. So there you go. This is what I would recommend using just total column references as opposed to name to raise or fixed a race so that you go now we have a really, really efficient, be look up function 8. The INDEX Function: the next look up reference function that I want to share with you is called Index and before even jump in, just hear me out. I know this is gonna feel like one of those functions where you read it. You see what it does, and immediately you think yourself. All right, Chris, why am I ever gonna need to use the index function? What it does is so simple that there doesn't easier ways to do it. Um, and you're right. The fact is, just like row and column. It's one of those functions that no one ever uses by itself. Um, for that very reason, it's it's so simplistic. But once you start combining it and using Index as a component of other formulas, you can open the door to start doing some some very cool thing so that promise it will pay off. Just hear me out. So the index function by itself. All it does is return the value of a specific cell within an array. So you're just saying aren't hey, Excel? Here's this array of data or array of cells. I want you to move down to row number five and column number three and tell me the value that you get. That's it. So, syntax wise, the index formula has three components. The race. So what range you're looking at row number, which is how many rows down do you want to go? And the column number, which is how many columns over do you want to move? So in this case here, you're gonna start from the upper left corner of whatever array you specify, and then move down to the road number and over two column number, and Excel will tell you what it finds in that. So so here. We're saying, Let's index the array from a one through C five. Let's move down to the fifth row and move over to the third column. And when you do that, it returns 2 34 because that's the value that it found on the cell that it landed on. So again, very, very simple. Trust me, this will make more sense in a just a minute, so hold onto your hats 9. The MATCH Function: All right, Next up, we've got the match function, which just like Index, it's very simple you not likely at all to use it on its own. But rather is a component for more complicated nested formulas. So the match function kind of works in the opposite way of index returns the position of a specific value within a column or row. So in plain English, basically, you're saying, Alright, Excel. I'm looking for this value, whether it's a word or number or whatever, within this column or within this row, and it will return the position of that value. So, um, three components the match function. You've got your look up value, which is what value. We're trying to find the position off your look up array, which is where are you looking? And note It has to be a one dimensional array. I'll tell you why, in just a second, the third component is the match type. So if you're looking for the exact value, which you will be 99% of the time, just put it zero. If you're looking for something similar, you can use it one or negative one. Here s 02 examples here in the first case were matching the word pliers in column A eso. It's looking through column A starting at the top and working its way down until it finds that look up value pliers and return. Four because it found it in the fourth row down. In this case, we're saying match the number 66 within Row three. So a three there c three. So Excel starts in the left, moves towards the right until it finds a look at Value 66 returns three because it found it in the third column over Now, remember, I mentioned it has to be a one dimensional array. The reason that's the case is that if we said, you know, match the number 2.5 within the array from a one through Be five, how would Excel tell us the position of the number 2.5, in this case? $2.50 within a three dimensional array? Right. There's no single number that will communicate the position of that value, which is why you have to say, look within a single column or a single row and Excel will tell you the road number or the column number in which the value was found. So that's match in the next lecture. Gonna talk about how index and match can be combined to do some epic stuff. 10. Using INDEX & MATCH Together: all right, so we talked about Index. We talked about match. Now it's time to talk about how to combine them to do some really cool stuff. In fact, when you combine them, they act almost like a look up function, but in some cases with even more flexibility. So they confined values in any column or any row within a given array. So let's take a look at an example. It's a little tricky to wrap your head around at first, but once you break it down into its individual pieces or components, you'll see that actually makes quite a bit of sense. So what we're gonna do is we're gonna write an index function, but we're gonna nest match functions within it. So in this case, we're saying our Excel. Let's find a specific price within this little price array from B to through D four. So it's gonna say index that array be to 34. But rather than just saying, you know, move to the third row down and the second column over which is giving it very fixed directions, I want to make it dynamic. I want the user to be ableto input. Some value that will determine what cell we land on within that array. And that's exactly what the match functions are used for. So if you recall the first piece of the index function is theory, which is B to 34 the second piece is the road number. So how many rows down to on a move? In this case, the number of rows down that we want to move depends on what products you care about sweater, jacket or pants. So what we can do is create a match function that says, Okay, we're gonna let the user enter whatever product they're looking for in Selby six and we're going to say, all right, match that product within the range from a two through a four. And then the answer is going to tell you how many rows down to index and then similarly allow the user to select what size they're interested in. Small, medium or large. And when you insert that into a match function based on the array from B one through D one , that will tell you how many columns over to move so it looks kind of like a beast of a function. But when you consider each individual match formula kind of starting inside out and think about what the output of each of those match functions is. You end up with just a really simple index. So in this case, if you've selected pants and medium, this is just going to save Index B to 34. Go down to the third Rome and the Second Column and return the number 30 because a medium parent pants cost $30. So let's jump into Excel and actually write one of these things from scratch. Same exact concept. Here. We've got a hypothetical price checker tool. In this case, we have five different size options and five different product options, and our goal is going to be to populate this cell here with the price I'm using an index function with match components to pull a certain or specific price out of this array. First thing we need to do is add some data validation because I want the years will be able to drop down the options for products and the options for sizes rather than having a type it in. So I'm just gonna selected be 10 going to data data validation allow a list. And that list is just going to be the list of products that you're able to choose. So like, OK, as you can see, that's worked. Same thing with C 10 data data Validation. Allow a list. And the source for that list is going to be all of the sizes that you can choose in this case, Those live and see 2 32 So I'm just gonna use that list, OK? And now I'm able to select any combination of products and sizes, so that's step one. Step two is writing the index match function. So we're gonna start with equals index. And what a rare re indexing It's gonna be the total array of prices that we can choose from . And since this formula is only gonna live in this one cell, I can either press that four to fix that or it can leave it relative doesn't really matter in this case, but now I'm gonna comma over now, excels looking for the road number. So how many rows down to move? And again, it doesn't make sense. Just tell Excel, you know, Row 123 or four because it's a function of what product the user has selected. I'm so I'm gonna insert a match, function here, say, match the value that the user is put in Selby 10. And where am I gonna look for it? In the list of products from B three to B seven. So that right now I have socks selected. This will output the number one because it's first in that array. It's in the first row within that array. And so the index function will stay on row one, which is what we need. Last but not least, match type. I'm gonna do zero, because I want an exact match here. Close that off in that match function is done. Comma over. Now I'm in the column number component of my index formula, so I'm gonna just insert another match function. Now we're gonna look up what size the user cares about or what size the user has selected. Um, come over to the look up array, which is gonna be the list of sizes from C to G two and then again exact match for match type. Close that off to finish the match function, and then one more parenthesis to close the index function, and that should do it. So I get for 25 right now. Have small socks selected, which looks right, and I could just, you know, Papa rounds different values to make sure it's working small pants. 25. There we go. Extra large pants or 28 extra large T shirts. 17. There you go index match. 11. Combining MATCH with VLOOKUP: So I want to talk about one more application of the match function that can be incredibly helpful to save time and work more efficiently with look up functions. So we're gonna jump back to that look up formula that we wrote in the earlier lectures. So I'm on the hitting data tab. I'm looking at cell P two on and I know you're probably thinking, What else could we possibly do to this function? We've worked on reference types. We've changed different table arrays. We've wrapped it and if error. But there's one last piece that we can automate to save ourselves a lot of time in the future, especially if we're gonna replicate this function and a number of places. So the piece that I'm referring to is the column in Next Number or the number four. If you recall when we first wrote this Siris of look up functions, we applied the first function we wrote over to the five columns to the right. So we applied the formula from Colin P to Q R S and T, and the only thing we had to change toe update those functions was the column index numbers . So we changed the 4 to 55 to a 66 to 7, and so on and so forth. We can do even better than that. And we can replace this column index number with a match function that will do the same thing automatically. So what I'm gonna do is delete the four here and I'm going to write match function. So match. And what look up valuer looking for? It's gonna be the name of the variable or the variable column header that I'm looking for in this case Pos or position. And where am I gonna try to find that in the head? A row of my look up array, which is in the fielding data tab. So it's gonna be fielding data a one through H one. And then remember, the third piece of the match function is the match type will be zero and closed the match function off. So now what I'm doing is, instead of just saying, give me the value from the fourth column, I'm saying give me the value from whichever call him in which you find P. O. S in the hetero, which in this case would return the number four just like we had. So when I hit enter, you see, it returns p for pitcher. Everything looks good. But before I apply this down and over two additional columns, I've got a little bit more work to do in terms of my reference types and setting them properly. So in order to apply this function down without screwing anything up, I need to make sure that the references in my match formula are properly set. So right now, I just said everything as relative. But what we need to do is if we leave the P one relative than if I drag this formula down one row, it's gonna try to match whatever values in P two, which is nonsense. So I need to cycle through f four. I mean, hit it twice until I just fixed the road one. Now, when I drag this down, it's gonna always pullin whatever the header label is in the first row, so that row number will never change. Similarly, regardless of which variable or which header I'm looking for, those will always live in a A one through h one. So I'm gonna fix that completely. And then the last piece that I need to do here is fixed the A through H reference in the look up table. That way I can drag this formula to the right without the eighth or H shifting through something fixed the A through H, and that should just about do it. So hit. Enter. Test this by applying it down. Everything populates correctly. That's great. And now here's the key. When I apply this over, I shouldn't have to change anything. It should all update properly, which it does now. What I'll do is I'll just take those four, apply them down. It's calculating. There you go. So, as you can see, the only thing that's changing now in my look up formula is the look up value in my match function. So in this case, it's looking up. The put outs header name in the Fielding Data array, which is in the fifth column over in this case, it's looking up are one which is the assists header name, which is in the sixth column over and so on and so forth. So it's doing the exact same thing as our original column number, except now it's completely automated, so we can write one look up function and apply it across a number of columns and a number of rows without having to touch a thing. 12. UPDATE: VLOOKUP Correction: So a few months after publishing, this course student messaged me and pointed out there was actually a mistake in the look up exercise section, and he's absolutely right, and I'll show you what's going on here. If you go into the fielding data tab in the look up reference workbook, you'll actually see that there are multiple instances of certain player keys, and the reason that happens is because certain players may have played in multiple positions even in the same year. So, for instance, Bobby, a brave in Rose four through six, is registered as a left fielder, an outfielder and a right fielder, all generating the same key because position isn't included. And what that means is that our look up function is searching for his player ideas. Player key, stopping at row for and returning the put outs, assist errors and double plays associated with the first instance of the key, which returns no data. And if we jump to the hitting tab, Bobby, a brave, is right here in Row four, and as you can see, he's got zeros across the board, even though that year in 2010 he had 240 to put out seven assists, six errors in two double plays. That data is invisible to the V. Look up because now we're dealing with a one to many relationship. We've got one key in our hitting data, and three keys here are fielding data. So how do we deal with this? One option would be to actually transform this fielding data set and aggregated at the player in your level. In other words, remove the position, field and aggregate or some the metrics and columns e through H. But there's an easier way, and it's actually to use the some ifs function that we covered in the stats formula section of the course. So let's jump back to the hitting tab. I'm going to copy the column headers and cute 30 paste them starting and column you. I'm gonna give him a different color just so that I can maintain my look up match functions over here and compare them side by side against this new approach, using some ifs. So I'll start by typing the sun. This formula equals some ifs. Now, if you recall the first piece, the first component of the summits function is the sum range What's the data that you're trying to? Actually, some here. In this case, it's my fielding data. Colin E. Which is put out's gonna leave that reference relative, and I'll show you why in a minute. The second component is the criteria range. So where am I looking to meet some criteria? Well, it's a player key, which is column A I'll press F four to lock that column in and then the last piece criteria . One. What criteria does call him a need to meet? And that's going back to my hitting Data Player Key, which is and sell eight to. In this case, I'm gonna fix just the A just the column reference so that my rose concious down as I apply this formula down. So there you have it, a press enter and apply it down. Now that is calculated, you'll see a lot of matching values with some exceptions, So one exception is Bobby, a brave in Row four, where we had been recording zero put outs with the V. Look up now we're recording 242 because we're essentially summing all of the values in column e where column a equals brave B 0012010 of which there are three instances a similar case with next player. There were three instances of his name as well. In his case, he generated 16 put outs at second base, 11 at third base and 16 at shortstop, which totals 43 put outs With the V look up approach, we captured 16 which is only the first instance. But now that we've shifted to a summits approach, we're capturing all of those put outs, all 43. And because I set my reference types properly, What I can do now is even just shift this to the remaining columns. Since I know these columns are sequential and that allows me to basically just allow that column eat a shift to F, G and H, and I could just grab those cells DoubleClick and apply them down. So there we have it. We have accounted for the fact that we no longer had a 1 to 1 relationship with our key, and we've achieved the results that we're looking for using a statistical function instead . Now, the one caveat I will make is that this work, because we're trying to return numerical values here, which some ifs can understand. If instead we're trying to return position, for instance, which is a text field. There's no way I could have used some ifs to accomplish this, because Excel doesn't know how to add P and to be an L f or any other text string. So again, if you're working with values, sometimes you can use the look up or some ifs interchangeably. Other times you'll need to use just one or the other. 13. The OFFSET Function: all right, so the last look up reference function that I want to talk about It's called Offset offsets kind of a lesser known function. But it's a really useful one when it comes to doing some kind of unique, powerful things which will show you in the next lecture. But basically just to give you some background, it's similar to index. It returns the value of a cell within an array, but it can also return a reference to a specific range of cells, which can be very useful when you want to create dynamic raise. And I know that sounds kind of like gibberish. It will make more sense in the next lecture at promise. So basically, sin tax wise offsets starts just like an index function. You determine a reference, which is what's your starting point? Typically, that's a single cell reference, and then you have a rose component, which tells you okay, how many rows down should I move? And then columns, which is how many columns over from the reference should I move on? And if you just leave it at that, it will function exactly like an index. It will give you the value of whatever sell it lands on. What makes offset different is that it includes these two additional components height and width, uh, which lets you return a multi dimensional array rather than just the value within a single cell. So these are optional, which is why they're shown surrounded by brackets. And if you leave those blank, if you just close out your formula after the columns indicator, uh, again, it will be just like an index on the height and width will default toe one and one, so you'll return the value of a cell that's one row tall and one column wide, quick pro tip Here. I'm showing you all these functions like Offset Index Match, which have very specific on and in some cases, very particular applications. Don't get too caught up in using them just for the sake of using them. So don't use an offset or in the next match, when a simple V look up will do the trick. So with that, that's how the offset function works. I'm gonna show you how it can be used to build a really cool dynamic scroll in chart in the next lecture, so get ready 14. Combining OFFSET with COUNTA: So before we dive into the squirrel chart example, which is really, really cool, I promise. I want to show you one other very common application of the offset function, which is to combine it with a simple stats function called Count A. Now there's an example of this in the homework exercise at the end of the chapter, but I want to show you a very basic example just to give you a sense of what it's all about . So in this workbook that I'm looking at here, obviously it's extremely complicated. I've got six items in a list in column A What I can do is use the count a function type equals count day to count the non blank values or cells within a range. In this case, my ranges, the entire column A. When I close that off, it will return a six since their six non blank values right here and call him a now, what does that have to do with offset? Well, what if we wanted to return the last item in the list? No matter how long that list is? Um so let's say last item and right here in D two let's start with an offset function this time. And so what we're gonna do here is we're gonna offset from the start of the list 81 And the number of rows we're gonna move down is account a function. So count a essentially means that we're making this offset function dynamic so that it can identify the last item in the list even as data is added or removed and the size of that list grows or shrinks. So we'll take the count day of the entire column A which we know returns a six. And now, before we move on, this is telling me that I'll move six rows down from a one which would take me to a 234567 which is blank. We've gone one road too far. So all I need to do to account for that is Adam minus one right there. And I don't want to shift any columns because I only have one column of data. So I'm gonna put zero there and I could ignore the height and the width for now. Just close off my premises. So now when I hit, Enter tells me my last item is a lime, which is pretty cool, but the beauty of it is that I can add values here and that value from D to changes accordingly. So grape, cherry and same goes as I delete values that updates as well, so that's pretty cool on its own. The other really valuable application of this is actually with charts and graphs where you can define your source data using an offset with Count Day, so that as you add new data to your source array, your chart will update automatically. So for more info on that, check out My Excel date of is advanced charts and graphs course, and I'll show you some demos of how that works. But there you go, basic combination of Offset and Count a. 15. PROJECT SHOWCASE: Using OFFSET to Create a Dynamic Scrolling Chart: All right, So it's time to put Excel to the test. Let's see what we can do to create a chart unlike most people have ever seen in Excel. Go ahead and open up the Section five Look up reference file. I'm going to jump to the last tab here, Squirrel chart tab. And our goal here is gonna be to create a chart with a dynamic data reference. We're gonna do that using the offset function. And the goal is gonna be to create a chart that we can zoom and squirrel to change the view of data that we're looking at. So we're gonna step beyond the static charts that 99% of Excel users are out there using so a little bit of housekeeping items. The first is that we're gonna use scroll bars. And to do that, you need to insert them through the developer tab. So take a look at the top of your cell screen if you don't see the developer tab where you need to do And this is in 2013 eyes going to the home tab, Click options customize ribbon. And then over here on the right, you should see a check box next to Developer years is likely on checks to go ahead and give it a check. Press OK? And you should see it pop up. Um, if the user experience is a little different and excel 2010 or for using an older version, honestly, just google it and make sure you have your developer tab activated. So I'm in Developer. I'm gonna go to the insert button and find this scroll bar form control. Click it. I'm gonna drag out one next to this scroll line purse, OK? And I'm gonna do the exact same thing and drag a second next to that zoom line. Uh, and if you want to edit the appearance of them, you can control click them and made that one a little fat. And I'm a perfectionist, so I'm gonna want some kind of it's close to Identical isn't can. That's fine. So now we're gonna do is edit what the's squirrel charts actually do. So, um, I'm gonna right click the 1st 1 and click format control. So this is basically saying, what values can this crowbar take? What sells air they linked to basically what are these scroll bars gonna control. So in this case, the scroll Scroll bar. I want to have a minimum value of zero. Gonna set the maximum value to six increment of one and page change. That's fine. I don't really care about that. The cell link is going to be cell D 16 which is right next to the scroll index text. So that's it for that one. I'm gonna right click the zoom Scroll bar, Click Format control again. In this case, I wanted to have a minimum value of one A maximum value of 12 and you'll see why in a little bit the one in the 10 or fine. In this case, the selling is going to be D 17 next to the zoom index text. So hit. Okay. And there you go. We've set up both of our bars, and now, if you just click through, you'll see that they're linked to the cell values in D 16 and 17. So that's step one. Step two is going to be to work with some named a race that are gonna feed our chart data reference. So I'm gonna go into formulas name Manager. This is my fielding name. or a I can just kind of leave it as is gonna create a new one. The first one's gonna be called Scroll Index, and that's just going to refer to sell d 16. So I pressed the little data selector. Press it again. So a squirrel chart d 16 okay. And create another one called Zoom Index, and that's gonna just refer to so the 17. So I've created two new named a race called Scroll Index and Zoom Index. So this is where it gets a little bit more complicated. I've got my school index. I've got my zoom index. The next piece that I need, um, is the dynamic array that's going to feed into my chart. And I'm gonna need at least two of these one for my ex values, which are the months and one for my y values, which we'll start with by plotting impressions. If we wanted to plot both impressions and clicks, we would need to named arrays for each series of why Values. So let's start with the X values here. I'm going to create a new named Array. I'm gonna call it X values and note exactly how you write these name to raise because we'll need to use the exact same syntax in the exact same name with the same capitalizations and lower case letters once we feed them into the charts. So I'm using Capital X and in a lower case V. So my ex values are going to refer to a formula, and that formula is gonna be an offset. So type offset and my ex values live in column B. So my reference or starting point will be be three. And then remember the second piece of the upset function, which was how many rows down do you want to move? It's a little trickier now because we don't have the helper box kind of guiding us through the formula. This is kind of the Wild West. We're on our own here, so we need to remember how the offset formulas built. In any case, the second piece is how many rows down on. And that depends on what the value of the scroll bar is which lives in so d 16 which is tied to the scroll index named Array. So I'm gonna type scroll index here The second piece, How many columns overdue? I want to move in this case, my ex values or my months only living column B. So I don't want to move any columns over at all. So in type of zero there, the next piece is gonna be the height of the array that I want to return. Eso This is dependent on the zoom scroll bar eso whatever value, Uh, zoom Scroll bar has output into D 17 is the height of the array that I want to feed into the chart because that's the number of months that I want to look at any given view, either crunched down to one or two months or zoomed out to all 12. So that's just gonna be my zoom index named Array. And then last but not least, the width of my reference is just going to be one because it's one column wide close, the parenthesis and hit OK, and that's created my ex values field. Um, I'm gonna create my y values field in the exact same way, so it's gonna be an offset function. The only difference is that the starting point, rather than being B three, is going to be whichever field I want a plot here. So let's plot impressions for this example. So my starting point will be C three and then remember how many rows down is gonna be scroll index? Uh, how many columns over zero. What's the height? Zoom index and what's the with one? Close the parenthesis and hit OK, And now I've created an X values array and a Y values array along with my scroll index and my zoom index. So go ahead and close out of the name manager. The last piece that I need to do is populate this chart that I've basically set up a shell of right here. So just gonna right click Select Data. I'm gonna add a data. Siri's here. Serious name is gonna be impressions, since that's the field that I'm going with for my Lai values and the Siri's values, rather than just selecting an individual cell or range of cells here, what I'm gonna do is make a selection just to give me that beginning part of this reference because I want the scroll chart and the exclamation point. But I'm gonna delete everything following the exclamation point, and I'm gonna replace all that with why values written exactly as I had to find it. So uncheck that hit, okay? You can already see in the background things they're starting to populate. And now, in my horizontal or category axis labels in a press, the edit button and the range is gonna be again rather than this fixed array. I'm gonna make a selection just to get the starting point in place, delete everything following the exclamation point, and I'm gonna put X values here. My head. Okay, you'll see that our X axis has updated to show names months and there we go. So, as you can see, we're charting out impressions and now, as a move zoom scroll bar the width of our view, which is actually captured by the height of this array, eyes shifting. And similarly, if I shift the scroll index now our entire view, however wide or have resumed, we said it is moving from left to right in the chart. So now I'm looking at six months of data and I'm seeing how trends month over month as I zoom in, I can look at just a single date at a time or two months or three and so on and so forth. Um, so if we wanted to include clicks as a secondary, why value That's easy enough to do. We're just going to the name manager. Create a Y values to named Array, and then just change the starting point of that offset function to D three. And the rest will be exactly the same. Um, so there you go a scrolling presumable dynamic chart using the offset function. 16. HOMEWORK: Lookup/Reference Functions: All right. Nicely done. You've completed the look up reference chapter. The course, Definitely One of my favorites hopefully found some useful stuff in there. And for those of you who want a little bit more hands on practice, I've got some great homework exercises for you. So go ahead and open up the Excel homework exercises. Workbook, We're going to start on the look up functions Tab. We've got three instructions here. This is going to use the next two tabs in gray State abbreviations and state income. Once you've wrapped up the look up functions exercises, go ahead and jump right to the index and match. Example. And then last but not least, I've got some great demos for Offset and Count Day. Um, good challenging questions. Hopefully, it will help you really get a good grasp of how these functions work. So again, the answer file is available in the course. Resource is. But if you need help, don't hesitate to reach out. Good luck