Excel Formulas and Functions Tips 16 - 20

so now we're going to do vlookup hlookup there's a new one that's called x lookup and we'll do match and index for the next couple must know formulas so one that people really struggle with is called vlookup so i'll show you a couple ways to use the vlookup i'm going to go to excel now all right i want to go ahead and go to a different sheet it should be the very next sheet here i'll click on customer list here you see a list of all the customers it has the customer id the company name and the contact name everything about the customer is on the customer list sheet then click on the order list over here and this has a list of all the orders as the order id and the customer id and the employee id so everything about the order is on the order list sheet notice on the order list sheet is showing the customer id but not the company name so if i want to show the company name here i'd have to take the customer id from this sheet look it up on the other sheet find the right one and then return the proper company name back to here well that's exactly what the vlookup does let's try it so i'm going to insert a column at column c and over here i'll type in company name i'm in cell c2 of course you can type in the formula but in this case we'll do it the other way so i'll pick in the formulas menu so these next few are going to be under the lookup and reference category those previous ones a lot of them were under the logical category like there's the if and the end and the or some of those might have been other math and trig like here we have average if and some if uh it's probably going to be down there okay so you know there are among different categories but in this case this is going to be these next you will be under the lookup and reference category and then the last couple of functions that we do today will be under the date and time category but let's go with lookup at reference and then i'm going to scroll down and use one that's called lookup v actually means vertical in a couple minutes when we did the h lookup h means horizontal it depends on how the table is laid out so click on v lookup okay good the lookup value is what we're trying to look up i'm trying to look up um the customer id in cell b2 so let's just say b2 the table array is where we're looking usually this is the one that's on a different sheet it doesn't have to be on a different sheet but many times it is so i'll pick on the sheet that's called customer list now for the vlookup you have to highlight the column that you want to match and you also have to highlight the column that you want to return so for that reason i'm going to highlight column a at the top and then drag it over to column b so that should say customer list exclamation point a colon b again when we see the exclamation point in the formula it means you're looking at a different sheet i want to match column a that's the first column of the table but i have to include column b because i want to return that so the column index number is calculated like this in the table column a is column one and column b is number two so i'm going to come over here and type in the number two for the column index number which means i want to get the second column of the table which is going to be the company name that's why i have to include column a and column b here let's see what range lookup says range lookup is a logical value that means it's going to be the word true or the word false to find the closest match you type in the word true there to find an exact match you type in the word false so i'm going to type in the word falstar in a few minutes i'll show you one that i'll have the word trip so for the lookup value i have b2 that's what we're trying to look up that's that customer id over here the table array says customer list column a through column b so i'm going to try to match column a and then i have to include column b because that's the column i want to return the column index number says 2. i want to return the second column from the table which is really column b which is going to be the company name and then the range lookup says the word false which means i want to do an exact match i'm going to click ok there's the vlookup formula so you can always type it in or we found it under the lookup and reference category i'm going to expand that let's think about what it did it took v-i-n-e-t it looked it up on the other sheet found the right one and then brought the company name back to here so it looks like the vlookup did this job i'm gonna go ahead and copy that down and now they all filled in and by the way those would all be relative formulas so let's see what the word false means at at the end i'm going to come over here and cell b2 and i'll just type in aaa and notice how it says n a n a means not available or not found we were trying to find an exact match it didn't find an exact match so it says n a so let me put that back to say v-i-n-e-t good and now it found it so that's the first variation of the vlookup and that's going to be over here in the slide deck on slide 10. let's go to slide 11. now this would be a variation of the vlookup that will have the word true at the end let's see what that one does so i'm going to go back into excel now now i'm going to go to the sheet down here that is called vlookup so in this case it's trying to look up the salary and find the tax rate for that salary this time the lookup table is on the same sheet okay so there's the vlookup vlookup b2 i'm looking at the salary over here this time i have the range specifically laid out it says d2 through f7 that's where we're looking but i have in light blue over here the number three means when i find the right one i want to go to the third column one two three so it's going to return the tax rate that's why i have include column f over here so i can go three columns over now notice how the fourth parameter is not even there this time the fourth parameter is optional let me show you why it's optional i'm going to click on that cell and i'll pick on the classic fx over here and then you see the parts of the formula the ones that are in dark black are required and the ones that are in light black like that one means it's optional so that has a real purpose the ones that are in dark black are required and the ones that are in light black are optional so i'm going to click where it says range look up and see what that says range lookup is a logical value means it's going to be the word true or the word false to find the closest match you put the word true there or when you omit it is the same thing as saying in the word true so when that fourth parameter says the word true or when it's not there it still means that we're true means we're going to match the range to find an exact match we'll type in false so i'll click okay so let me change this to a different salary i'll type in 30 000 and it gives us 31 percent if that vlookup had the word false at the end then it would say n a here because uh 30 000 is not on this list but the fourth parameter is not there which means it's going to be true which means it's going to match the range so everything between 27 3 and 58 5 will give us 31 percent if i come over here and type in 60 000 then it will give us 36 percent because everything between 50 85 and 131 8 will give us 36 percent so that's how we use the vlookup when when um when we have a true type of situation or when the fourth parameter is not there it still means true let's go to our next important function which is going to be called hlookup because i'm going to go to the next sheet here and now we have h lookup h just means horizontal the table is laid out going across but it's going to have the same syntax as the vlookup if i come over here to cell b3 there's the h lookup b2 is the cell that we're trying to look up this time the range is e1 to j3 what i have in light blue over here now it's trying to match the first row whereas with the vlookup you try to match the first column so it's almost at the table it's on the side the three means when i find uh the right one we're going to go three rows down notice how the fourth parameter is not there so if i click on this fx just like with the vlookup the fourth parameter is optional and when it's not filled in it means true which means it's going to do a range match so it's very similar to the vlookup so in this case i'll type in 40 000 for the salary and it gave me 31 because everything between 27 3 and 5 will give us the 31 so the h means horizontal it's when the table is going across but i've seen that uh just as many times as the vlookup so that's another important function that you should know h lookup now everybody if you have a newer version of excel like i do then let me show you the next important one in fact let's go back to our slides for a second here's the vlookup now this one has the word true at the end or remember you can leave the fourth parameter off and it still means the word true which means it's going to match the range and then on slide 12 there's the h lookup same kind of thing except the table's going across so it's horizontal go to slide 13. now if you have a newer version of excel you can use x lookup i think x lookup is very powerful and i think it's simpler to use than the vlookup and the hlookup it can actually replace both of those let's try an example of the x lookup so i'm going to go back into excel and i want to go back to the other sheet i'm going to go back to the order list sheet where we did the vlookup i'm going to insert a new column here i'm going to right click on column d and i'll pick on insert and over here i'll type in contact me [Music] all right in cell d2 we'll do the x lookup and this is also going to be under the lookup and reference category of your functions so i'll do look up a reference i'm going to scroll down and if you don't have an uh if you don't have a newer version of excel you're not going to see the word x lookup it's about a year old so you'll have to update to a newer version of excel if you don't see it i click on x lookup the lookup value is still the customer id over here in cell b2 i'm going to click where it says lookup array i'm going to go back to the customer list sheet here i just have to highlight the column i'm going to match which is column a i don't have to include the additional columns it's just the column i want to match now i'll click on the space where it says return array and i'll click on the customer list sheet and i'm just going to highlight column c so i think they made it much simpler than the vlookup and you can actually use this to replace the uh hlookup as well so the lookup value says b2 that's what we're looking for the lookup array is just the customer list sheet column a that's where we're looking and the return array is the customer list sheet column c that's what we're returning and i'll click ok and now it found the contact name based on that customer id of course i'm just going to copy it down right so there's the x look up i think it's very very useful i think it's it really is a must-know formula if you have a newer version of excel all right that can actually replace the a any vlookup and and the h lookup i think it's a lot easier to use so we're going to go back into our slide now and that was on slide 13 and that was the 18th must-know formula so let's go to slide 19 i mean slide 14. now we're going to talk about the match and index so match is going to be the 20th important function and index will be the 21st matching index is another way to look at the information on a table and many of your legacy spreadsheets will have the matching index in there it's been around for a long long time going forward i would recommend that you use the x lookup the x lookup can actually replace the matching index as well but the matching index it's in so many different workbooks so i recommend that you use this one as well or at least learn it so let me go back into excel now now there should be a sheet over here that is called match and index all right again in real life you you can use a much bigger table i want to try to match the row that we're looking for we're going to try to match the column that we're looking for then the index will find the intersection of those two so that's why it's called match and index because they go together although they're really separate formulas so in cell b4 these will also be under the lookup and reference category i'll choose lookup and reference and i'll pick on the word match the lookup value is what i'm looking for which is the word july in cell b1 the lookup array is where i'm looking i'm looking in column d i'm going from d1 to d14 that's where we're looking now if you go to your help screen you can always go to this window and click on help on this function for any of these then the match type is looking for one zero or negative one it'll tell you that zero is for an exact match so usually with the match i'll have a zero there my lookup value is b1 the lookup array says d1 to d14 and the match type says zero i'm gonna click okay so it says the number eight here because it starts here this is number one one two three four five six seven eight so july is the eighth row remember you would use this on a much bigger table then i would use another match to match the column that i'm looking for so again i'm going to pick on the lookup and reference category and i'll pick on the word match the lookup value will be the word sprockets in cell b2 the lookup array is we're going to go across this time i'm going to go from d1 to h1 and for the match type i'll type in zero my look of values is b2 the look of array says d1 to h1 and the match type says zero so now we know that july is the eighth row and that sprockets is the third column going across then the next must-know formula is going to be the index so it is a separate formula the the index will show us the in the intersection of those two so i'll click on lookup and reference i'll pick another index now there's two variations of the index function so i usually use the first one and i'll click ok for the array you're going to highlight the entire table i'm going to have the entire table this time and then for the row number it's going to be the first match that we did which is over here in cell b4 and the column is the second match that we did which is over here in cell b5 so the array is the entire table d1 through h14 the row number says b4 which is where we did our first match and the column number says b5 which is where we did our second match and i'll click ok it says three three three seven is that correct july 1st sprockets is 3 3 3 7. so usually i'll do two matches one for the row that we're looking for and one for the column and then the index will find the intersection of those two so that's called the match in index those are other must know formulas here in excel let's go back into our slides and we'll see the first match on slide 14 the second match on slide 15 and the index on slide 16. now if i go to slide 17 we already did the count if and then 18 and 19 have the f's okay so now i'd like to show you a couple date formulas and i think these date formulas are going to be useful for you as well and i think these are very important to know so that's why i include them in the class of course so i'm going to go back into excel all right thank you for watching this video if you liked it please like share comment and subscribe to my channel if you really liked it please click on the thanks icon that looks like this below this video to support the channel please check out some more of my free videos you can also subscribe to my mailing list and find out about my other services by going to www.pc webinars.com thank you again you