All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples! - 365 MECS 08

welcome to Microsoft 365 Excel the complete story and in video number eight we're gonna do excel lookup and of course we're gonna do worksheet functions X lookup filter switch index x match and we'll even see the mysterious power query and Dax lookup formulas [Music] 28 examples of lookup formulas yes indeed doing lookup is so common and there's so many different types of lookup we might look up a price a tax rate a full table a column a record and much more now before Microsoft 365 we had to use the vlookup function the index function and the match function but with Microsoft 365 Microsoft invented X lookup and it replaces all of those functions in one function and does so much more now there are a few situations where we use other functions in particular filter can do something that X lookup can't filter can return multiple matches and we use filter a lot as a lookup function we'll see these other functions each one has a particular use that is a little bit better than x lookup and we'll see how Now power query and Dax lookup is different than how it works in the Excel worksheet now let's start by going over to the sheet X lookup now on this sheet I have some notes about X lookup X lookup has six arguments here's some notes these are also in the PDF notes we'll see how to use examples of all six arguments and a lot of the cool new things that X lookup can do as compared to the old vlookup function now we're going to start off by talking about exact match lookup so let's go over to the sheet em3 our goal in cell C5 is to look up the quad product and get the correct price for the quad product and bring it back to the cell now if you were doing this manually what would you do well you'd first look at quad remember what it is find the product column then look through it until you find a match it's in the third position so then you jump over to the price column and in the third position you get 43.95 that's the price you retrieve it and bring it back to the cell now if you know how to do that manual process then using a lookup function is going to be easy we're going to type equals XL I see it in the blue drop down so I hit tab now there are six arguments when we're doing exact match lookup that means I'm trying to find the exact characters q-u-a-d in this column right here for exact match all you have to use is the first three arguments lookup value that's the thing we're going to look at remember and go try and find a match now I type a comma look up array that's the column that contains the items where we're trying to make a match this is an Excel table so I'll click at the top there's the product column comma return array that's the column that contains the items that we're trying to retrieve and bring back to the cell now for the return array the price column it has to have the same number of rows as the product column if it doesn't we get get an error now for the time being we don't have to use any of the other arguments because the default for match mode is exact match lookup close parentheses control enter and there we got the price if I type Aspen control enter it got the right price if I type quad again control enter n a well if I hit F2 that's five characters quad here is four characters now for n a F2 we could put some sort of message in the fourth argument the if not found argument we could put a text message or sometimes in math formulas you put zeros but I'm going to click Escape for the time being backspace get rid of that extra space that's four characters so when I hit enter I get the correct price now anytime you do an exact match lookup and the lookup table is on this sheet or one of the other sheets the best thing to do to make sure that data doesn't get entered into this cell that's not in the product column is to use data validation and data validation will allow us to add a drop down arrow to validate that only product names from this column are allowed to be entered into the cell so with the cell selected I go up to data over to data tools I'm going to click the data validation button this opens up the data validation dialog box by default it allows any type of value but we want to click the drop down and there's a bunch of amazing options we could limit it to whole numbers decimals list is the one we want but there's a bunch of options there we're going to select list the source well it's our product column and so from now on we're not allowed to put anything in this cell unless it's one of our products now we'll have an in cell drop down and ignore blank now you can put an input message that means when you click in the cell there'll be a message also you can put an error alert let's try this there's the title there's our error message click OK and there's our drop down we can select a product from the lookup table if we Select Car Lota it gets the right price however if I type quad with a space and hit enter there's the error message that we got to create title and message cancel and now I'm going to select quad from the drop down now the only drawback to data validation list is if you have quad over here with a space and you copy remember copying takes everything and replaces what's ever there so if I control V now I have an N A the border is gone and so is the data validation so be sure to control Z and don't copy and paste over data validation drop down list now before the X lookup function we had to use the vlookup function it did have the same lookup value but we had to use the table the First Column always had to have the items to match we had to put which column has the item we want to retrieve and because the default was not exact match we had to put a zero with X lookup it's significantly easier we just put lookup value the column to make the match and the column of the items we want to retrieve now what we did in this first exact match lookup example is we looked up a single product the lookup array is a single column with five rows and because return array is a single column with five rows when it finds a match it returns just a single item now in our next example look up a record we have a different scenario we're still going to do exact match lookup and look up a row but in the a return array if we give it all of these columns when it finds a match in the second row it'll return all the columns and this is a very common scenario where you're going to look up an entire record equals x lookup so we use our lookup value comma look up array well it's a single column it has four rows comma return array it's as many columns as we want but it's got to have four rows it's exact match so we don't need any of the rest of the arguments close parentheses and when I hit enter that is beautiful now let's change the ID and just like that that record is looked up now before X lookup it was much more difficult to look up and retrieve a record we would have to use vlookup and columns together or index with two match functions which means this is a great illustration of how much easier lookup is with X lookup now this is looking up a record which is a row you can also look up a column now in this example we want to select one of the days and just add all of the sales now this isn't a proper data set where we have a column with our day and sales this is set up sort of like a cross tab table but it's no problem if we get data like this and we want to look up a particular day column and add we can do it equals x lookup lookup value we'll look up day comma lookup array well it doesn't have rows but look at this one row with four columns comma so in this case the return array has to have four columns but if I give it four columns and lots of rows when it finds a match meaning the second column it'll return all of these rows close parentheses now when I control enter it spills but watch this I'm going to move it using my move cursor just for a second so you can see that it actually did look up the column control Z since our goal is to sum after the equal sign s-u-m X lookup is just going to deliver some numbers to sum and some will add if I use the drop down to look up day one bam there's the total now before X lookup we have this formula we had to use index and match inside of sum which is not too much harder but we did have to use the Obscure trick of leaving row number empty or putting a zero so definitely sum and X lookup when in terms of easiness again now the first three examples look up a single item a record and a column all of them were exact match now we want to go over to the sheet eat next smaller and talk about approximate match now approximate match lookup is the term we used in the old days with a vlookup function now we call this type of lookup exact match or next smaller and it's very common we might be looking up earnings needing to find a match and return a tax rate we might need to be looking up sales or earnings find a match and return a commission rate this type of lookup also Is How We Do grading at Highline college and here's how it works I need the tax rate here so I look up this and I look through this column to find an exact match or the next smaller so there's no exact match but this is the next smaller and the reason it's exact match or next smaller is because the lower limit is included so a thousand gets into this category which is a thousand up to 1999 and 99 pennies but every thing in between those two is also included so of course 1 200 fits between this lower limit and upper limit so 10 percent is returned now we equals x lookup there's the lookup value comma lookup array while we're trying to find a match in this column comma here's the values we're trying to return and because this type of lookup is not the default we have to type a comma you could put something there if you wanted to have the formula deliver something when it didn't find a match in this case the only number that wouldn't return a match is something below zero but I'm going to skip it and here it is exact matches the default but I want exact match or next smaller so I put a minus one close parentheses and enter and sure enough it gets the right tax rate 2750 it gets fifteen percent how about exactly five thousand it gets 20 percent and guess what this category right here is 5000 or more so if I put in nine thousand sure enough it's 20. the only situation where I'm going to get an error is when I enter a value less than the lower limit of the first class so if I enter minus 100 I get an n a now we could come back here and in double quotes we could put something like negative numbers not allowed in parentheses and sure enough that would work we could also F2 since the tax rate would be zero get rid of this text and just put a zero now before X lookup we had to use the vlookup function or the lookup function now I will teach you how to use the lookup function later in this video because in cases where this column is sorted the lookup formula is actually faster and easier to create than the X lookup function now the one advantage that X lookup will have overlookup is both lookup and vlookup require that approximate match has the First Column sorted smallest to biggest if I were to sort this the other way largest to smallest these get the wrong answer but X lookup will work no matter how this column is sorted now control Z that makes X look up the safer function to use when we're doing exact match or next smaller however because most tax and commission tables are always sorted this way we will learn how to use the lookup function later now let's go over to the sheet vertical or horizontal now here we have a vertical grade lookup table and vertical means that the values we're going to match are set up vertically here we have a horizontal grade lookup table luckily with X lookup it can do either vertical or horizontal lookup we'll do X lookup there's the lookup value so 83 percent for GG comma we're going to try and find an exact match or next smaller in the grade percent column comma we're going to try and return a letter grade now this is not exact match so we comma for the time being I'm going to leave that blank comma and match mode we want exact match our next smaller so -1 close parentheses and there we get a B over here we have the same lookup value but look up array this is where we're going to try and get a match that is a horizontal range comma as long as the letter grades are horizontal with the same number of columns this will work comma comma minus one close parentheses and enter so in both cases vertical or horizontal we get a B now what happens when instead of 83 it's 43 percent well in both cases we get an N A now there's two different ways we can deal with it let's try F2 we can put something in this argument if not found well if it's anything below 50 percent that's an F so in double quotes we'll put F the other way to deal with this and this is how I deal with it in grade tables is we just want to make whatever the last category is the absolute smallest value well zero is the smallest percentage score you can get so when I zero that gets an F that just means everything from 0 up 2 but not including sixty percent gets an f and of course the top side that is ninety percent or anything above now in the old school days when we had a vertical table like this we'd have to use either look up or vlookup and then use if n a if we had a zero as our lower limit we'd have to switch over to hlookup the H is for horizontal or lookup actually could do both horizontal or vertical Lookup All Right Next we want to go over to the sheet next bigger now in this example we want to retrieve the drain pipe size based on land square footage so if we have land square footage twenty two thousand we need to get the pipe size required now we'll try and match the land square footage here in this column and notice this column represents the maximum land square footage for pipe size this will be the column we're trying to retrieve something from drain pipe size in inches now if we look up twenty two thousand there happens to be an exact match that means we would retrieve a six inch pipe size and bring it back to the cell but what if this is twenty three thousand remember this is the max land square footage well we can't use 22 000 because that's the max for a six inch pipe that means we need to go to the next bigger so this is the perfect job for please look this up find an exact match or the next Vigor equals x lookup there's the lookup value comma lookup array we're trying to find a match comma these are the values we're trying to return comma we'll skip over this for now now comma and if we arrow down this is exact match or next larger item so we can put a one close parentheses and enter and sure enough it got eight now why did it not retrieve the inch mark because this is actually done with custom number formatting now we'll come back to adding custom number formatting to this cell in just a second but let's test our formula four thousand we better get a five inch pipe what about three hundred thousand well we're going to get an n a and guess what this lookup table does not allow that value this is the max land square footage so that's the biggest possible value now we could go to data data validation and we want to allow whole numbers and then you could put 0 to 238 000 Escape or F2 in if not found we could put a message that's linked to the biggest value in this lookup table so in double quotes so I put enter a value less than or equal to all in double quotes and I'm going to join it to the biggest value in this table control enter and then we can make this bigger now we can make this message even better F2 remember in the text video the perfect function to take the underlying number and add comma number formatting is fixed we definitely comma want zero decimals close parentheses and now we have an even better message when someone enters a value too big 120 one two three and we get our 12 inches now the custom number formatting well these are all just numbers 16 12 10 and there's a way with custom number formatting to show the text quote quote so I have the cell selected control one custom since this is always going to be an integer we can just put zero that means we're not going to have any decimals and then if we want text we have to put it in double quotes and this might be hard to see but I'm typing a double quotes because remember text always has to be in double quotes and instead of putting a double quote I'm going to type two single quotes one two then I'm going to type it double quotes the reason I put two single quotes is because if we put double quotes it thinks that's the outside container for text so we can see a preview it looks like it worked we click ok now I change this to 500 and sure enough I get three inches now I don't think we're ever going to enter something negative but anything less than 3500 certainly can be accommodated with a three inch pipe now in the old days this is how we did it we had to use index and match and a minus one in the last argument of match but with X lookup we have a single function including a way to put a cool message all right enter now let's go to the sheet wild now what we want to do here is something called wild card lookup I want to look up Coca-Cola and get a match for Coca-Cola Incorporated so this is the lookup value but I need to find this and return the city Atlanta it's no problem just as we saw in the text video video number six we can use wild cards now when we do wild card lookup it can be called partial text lookup or fuzzy lookup also equals xlookup and I really need the wild card that stands for zero or more characters asterisks asterisks so we'll in double quotes put an asterisk and join it to whatever's in that cell and an asterisk on the back side that's the lookup value comma we're going to try and find a match here and these are the items we want to return and we have to comma comma and the last option number two is what we want so two for wild card close parentheses control enter now anytime you use wild cards you are not going to get the result you want a hundred percent of the time if I change this to Cola well there's lots of colas and when there's duplicates for either wild card or exact match it only gets the first item now we saw how to deal with this in the text video we would use partial text logical test and the filter function and then we could return all items that contain Cola so really if you're looking up Coca-Cola you're not going to type Cola maybe you type coca if you want Pepsi if we type pep we'll get the city coca and enter now the old school method we just used vlookup with wild cards now let's go over to the sheet look up first now here's our template we have employee dates across the top and they have to put an X for the days they work and our goal is to look up the first date worked on a project well that's going to be easy for us because getting the first one they're duplicates means we can use exact match lookup which does this by default when they're duplicates it can only get the first one so we'll use x lookup lookup value we're looking up an X we can hard code this in in double quotes because it's never going to change comma look up array relative range of cells as we copy the formula down to each new row we want this to move to each new row and in each new row xlookup will determine the position of the first X and when we come put into return array of the dates it can retrieve the first date now we're copying this down and this needs to be locked so we F4 now we could comma comma the default is exact match comma we haven't looked at this argument yet but the default is to search First to Last and of course the first time it encounters an item it takes that as the position so we don't need any of those arguments that's our formula control enter double click and send it down we got a F2 because we have relative and absolute cell references F2 everything's looking good now the old school method we had to use index look up the dates and for row number and index we had to use the match function to figure out the relative position of the X so of course match would say it's in the third position and index would retrieve ten six now looking up the first item is very very common but so is looking up the last so let's go to the sheet last now we have date sales are up in sales and our goal is to get for each sales rep the last sale and then down here we want to get the last day that they made a sale now this will be a simple x lookup formula as long as the table is sorted in the correct order because by definition getting the last means when I look up Sue that's the last one so we can use x lookup we're going to have two lookup values that way it can spill the result look up array while I'm trying to find matches here comma I'm trying to return sales values comma we'll skip that comma the default is exact match comma but we're not going to use the default first to last we're going to use -1 to search last to first so minus one close parentheses and now when I hit enter bam I get 1 2 to the last sales for each sales rep now if we sort this this will not work now before we create a formula to deal with the fact that we might sort this data set let's see how to look up the last date that each sales rep made a sale now we don't want to use xlookup because that depends on order and if we realize the dates are really numbers then what do we want we want the biggest number if there's a sue in this column if there's a chin in this column so if that's the perfect job not for a typical lookup function but for the max ifs function remember max range needs numbers and these are numbers comma criteria range all the sales rep comma and the criteria we'll put both names so this will spill close parentheses and enter of course those are serial number dates but if we highlight control 1 date that's good click OK there it is and if we sort this of course that works perfectly control Z now if we're going to look up the last sale we're going to have to use two conditions the particular sales rep and the last date well we already have a formula for this down here F2 I'm going to highlight all of this control C Escape control V now I'm going to hit F2 because of course the criteria is looking at the wrong place but we can just point with our move cursor and click and drag now that's one of the conditions for matching in this column we'll use these conditions to match in this column now instead of using X lookup and two separate lookup values because we're never going to have any duplicates where there's a name and the particular date we can use a hack by using the sum ifs function we can sum this column based on two conditions but because there's no duplicate names and dates the sum we'll just get a single number so sum ifs some range we're trying to get sales comma Max ifs is the date condition criteria range one we want the full date column comma that's criteria one the max date for each sales rep comma criteria range 2 comma criteria two and we can use sumifs because there's two conditions in both criteria one and criteria two so it will correctly spill the results and now right click sort these don't work but both of these do control Z now you could also use x lookup with two conditions but we haven't learned how to do that yet and I kind of like the sumifs better anyway we'll see how to do this later and old school we'd have to use a formula like this or this all right that's how to look up last now we want to go over and do the classic two-way lookup now here's a two-way lookup table straight from the IRS our goal is to look up gross pay find a match in this column get the row then look up allowances find a match to get the column and the intersecting value that 45 bring it back to the cell and that's the tax amount now we can use x lookup but we're going to have to use it two times and you get to choose should we look up the column first or the row it doesn't matter we'll try columns so equals x lookup we're going to look up the allowances comma lookup array notice it's eight columns comma return array well that return array has to be eight columns but it can be as many rows as you want this will look up this entire column now with allowances like this we have data validation on this cell so we're doing an exact match lookup so we don't need any of the other arguments close parentheses control enter and sure enough it looked up the entire column now that's a huge column because I have a bunch of rows hidden here but this lookup range right here is exactly the same size so F2 X lookup the lookup value now is going to be gross pay comma lookup array this same size column comma and X lookup is the return array now we're definitely doing exact match or next smaller lookup because 705 needs to find the 700 row comma comma minus one close parentheses and there's our w double X lookup to do a two-way lookup now we could look up the Row first get a match here and return a row control enter oops we got a spill error but we'll move it you can see it looks up the row control Z F2 now from that row we'll use x lookup to try and find a match for two in this row filled with columns comma X lookup in return array is exactly what we want close parentheses and we get exactly the same thing if I change this to 460 instantly I get a new tax amount of 13 bucks control Z now in the old school days we'd have to use either vlookup and match together or index match match now our next task after doing two-way lookup is we want to see what happens when we have two lookup values now our goal is to look up a price based on product and store so we have two columns that will determine the price and this is a common situation where we have a particular product in this case quad and it's sold at different cities or different stores and each one has a different price that means up here we have two lookup values Seattle quad we can see that the price we want is 39.95 but inside our formula we'll use the Ampersand to combine Seattle and quad into one lookup value and then we'll join with the Ampersand store and product column into a single lookup array so equals x lookup Seattle Ampersand to join quad and that order matters comma because if I come down to look up array and accidentally highlight product first well that's not going to work because City came first so we highlight the store or city column first Ampersand and sure enough we're allowed to join two different columns into a single column in fact we could highlight this and hit the F9 key and sure enough it looks funny inside the formula but this will work Oakland quad Seattle quad Tacoma quad we have single values in a single column control Z and of course lookup value F9 there's the Seattle quad control Z comma return array we're trying to get price close parentheses and control enter if we change this to Oakland we get the correct price control Z Now the old school method well we join in the same way but we'd have to use match and index Escape now the next task is going to be quite amazing we're actually going to look up a particular range in the lookup table now we have a lookup table for covid cases and I think I got this table from 2001 so it has months across the top and states on the bottom we want to be able to use three lookup values and so for example if I choose California I need that range inside the sum function control Z and we're going to use x lookup three times and the idea behind it is this I'm first going to look up this range right here notice E6 colon h10 that's how a range reference works that colon is called a range reference operator so with X lookup I'm going to look up the top cell E6 because yes if you put two x lookups and a colon between them it doesn't look up the actual value it looks up the cell reference then once we have that we'll use this in the lookup array and look up Nevada and Bam it'll give us the row now before we use that colon range reference operator let's see if we can look up for May the cell in the first row which will be that value then for August looking up in the last row I want to go and get that value so equals x lookup we're looking up may within all the months comma and we want to get whatever the cell reference is in the first row now of course when I close parentheses doing exact match and hit enter it looks up the value same here we'll look up August within here but this time we're looking up the value in the last row close parentheses and enter sure enough that's what we have now I want to show you a great trick I'm going to copy this in edit mode and watch I'm going to use control CC that opens up the clipboard and stores the element so I can use it whenever I want if Ctrl CC didn't work you have to come up to Home launch the clipboard come down to options and check this show office clipboard when control C is pressed twice now I'm going to come down here and now I just control C once I've stored both elements up that's the first cell reference that's the last one I come down here equals first cell reference right now of course it delivers a value but as soon as I put colon and the second cell reference now when I hit enter that is absolutely beautiful so xlookup can look up a lot of different things including cell references now we use xlookup we want to look up Nevada comma within this range notice this has the same number of rows as the range that we created with two x lookups and a colon so when I comma there it is that range we looked up with two x lookups sitting in return array at the end I close parentheses and that is amazing there's that exact range right there if I change this to California it's looking up the right range control Z F2 s u m at the end and enter so now for any range of months and state we can figure out what the total number of cases were now in the old days we'd have to use a much longer formula with many more different functions so we are definitely happy that X lookup first can look up cell references and with three of them inside of some we can look up any particular range we want all right that's it for X lookup it can do a lot next we want to talk about duplicates which is something we've already talked about because what function do we use when we have one lookup value duplicate matches and we want to return multiple items that's correct the filter function so we'll go over to duplicates now we'll just start off by looking at the old school method yes in fact this is what we used to do when we wanted to look up a single lookup item and return multiple records now the second book that I wrote control shift enter mastering accelerate formulas that whole book was about formulas like this but now now with my third book The only app that matters we don't have to do that anymore because we have the amazing filter function in Array I put everything I want to filter and notice that's 12 rows tall comma so we need an array of trues and falses or ones and zeros that's exactly 12 rows tall so we simply highlight the student column and ask the question how many of you are equal to GG close parentheses and that's it that's our amazing filter lookup formula when I hit enter sure enough I highlighted the wrong range F2 click inside a filter select array redirect it so we only have class department and grade and now when I edit and hit enter now I have exactly what we want we want to see Sue chin and Bam there are the records control Z now let's go talk about something we have already talked about also just like we talked about filter let's talk about the x match function and comparing two lists now on the sheet x match here are some notes about the amazing x match function these are also in the PDF notes let's go over to compare lists now we've already used x match a few times to compare lists but I'm going to repeat it here because it is such a great trick to have up your sleeve and because the x match function is a lookup function but unlike X lookup and filter it does not return a value it Returns the relative position of an item in a list so if I were to tell x match to find GG in this list it would return a 2 because it's in the second position now for lookup value we want this to spill so I'll put all the names comma lookup array I'm trying to find them and get a relative position in this second list now what we're doing is exact match so that's the default but just like X lookup there's two amazing arguments here in x match also comma match mode by default if you leave this out it does exact match and then just as we saw earlier with xlookup we can do exact match next smaller next larger or wild card comma we can also search first to last and last to First of course the default is first to last which is what we want so we can leave those arguments out that's our formula close parentheses and enter and a says not available I did not find this particular item in the second list a number says I found it in the top cell F2 we can use is number is number says I found it in the second list by the way if we use is an a it means I did not find this particular item in the second list but we're interested in which names are in both so is number close parentheses and enter and there we get our spilled array of falses and trues now if I come over to the side equals x match and I do it just one cell that's a relative cell reference comma and I'm looking it up over here locked close parentheses control enter and copy it down I get the same result but just as we saw earlier in the class we actually F2 we can use this exact formula in the conditional formatting dialog box and that dialog box interprets any number is true and anything else including errors as false so I copied it Escape highlight the range home over to Styles conditional formatting we want new rule use formula to determine which cells to format format values where this formula is true control V we'll add whatever formatting you want you can use all four tabs I'm just going to use yellow click OK click OK click ok now something went wrong I'm not sure what home drop down and because I already have a rule and I want to edit or check to see if it's working I'm going to click manage rules and sure enough this happens sometimes with conditional formatting we want to edit so I'm going to remove the double quotes click OK click OK and so now we have a spilled array of trues and falses and conditional formatting based on the lookup function x match and remember X matching is number when you want to find items in both is an A and x match when you want to find items that are not in both all right now let's go over to the sheet index now the index function along with match and vlookup were indispensable before Microsoft 365 Excel and the X lookup function but now X lookup can do almost everything that vlookup and index can do but there is still once to situation where I tend to use index let's go over to the sheet random now if our goal is to randomly select a name from a list X lookup can do it but this formula is much longer than if we use index now equals index we don't use the second option down here because that allows you to look up tables and as we'll see a little bit later using the Microsoft 365 Excel switch function is much easier than using this second option but the first option notice the array those are the items we're trying to look up and then there's two other arguments hey tell me what the row number is and if the items are across columns you could say which column number in addition when you use both row and column this function can do a two-way lookup now the reason this function is different is because we actually put a row number in column number not some match like we do with X lookup or even the match function and in fact in the old days we would always use match in the row comma and match in the column number because the match function delivers the relative position regardless if it's a row or a column now when we're randomly looking something up all we need is to randomly choose a number between one and however many items there are in the list in this case there's nine well there's a great function ran between actually Rand array delivers an array of random numbers whereas Rand between will deliver just a single number and that's all it needs bottom and top well the bottom is always going to be one and I don't know exactly how many are over there so I'm going to use the rows function to just tell me how many rows there are it'll count nine close parentheses close parentheses so write in row number we're going to randomly generate a number between one and nine close off index and that's our formula and we can hit the F9 key and randomly select a name now if we wanted to randomly select a bunch of names for example when I create a data set to use in a class I usually have a uni unique list of items and then I randomly want to select from that list and create a column in this case we just use the array comma and in row number instead of using Rand between we use Rand array now whereas Rand between has just bottom and top in this function it's called Min and Max there's five arguments in Rand array the first one is how many rows of random numbers you want for us that means the height of the column I'm going to link it here right now that 5 will determine five random numbers which will randomly pull five names when we later change it to 10 the height of the column will be 10. comma we're only doing a single column so the default is one we can skip it Min this is like bottom when we used Rand between we're going to put one comma Max is the top we'll do the same thing as before we'll ask the original column how many rows there are that will be nine and then comma we get to decide do we want decimal numbers or an integer we definitely want integer the default is decimal and by the way that means we wouldn't put min max or integer and Rand array would deliver random numbers between 0 and 1 with up to 15 digits but we want an integer which is true or one so Rand array will deliver five numbers between 1 and 9 and they'll be integers and that's it Rand array will deliver right now five numbers in row number that will instruct index to spill five randomly selected names enter and we could hit F9 and sure enough we get a new set of randomly pulled names each time if I change this to 10 F9 there's a column of 10 random names control Z alright so in the old days we used index with match a lot now we have a few uses but the main takeaway for you is index is the lookup function that allows you to put in not some lookup value that gets matched but row and column numbers all right let's go over to the sheet lookup now the lookup lookup function has a special place in spreadsheet history it's one of 21 of the original functions in the very first spreadsheet in history visit calc now we'll see two good uses that lookup can do that beat xlookup and we'll look at this first option where there's a lookup value a lookup vector and a result vector and then we'll look at the second option which only has two arguments now one drawback to look up is that if you use the lookup Vector that column must be sorted A to Z and if you use the array which is a lookup table the First Column has to be sorted A to Z and this function can only do approximate match lookup that's like exact match or next smaller this function cannot do exact match but there are a couple of really great uses for lookup let's go to the first sheet vnh for X lookup if we look up units and try to get a match here we are definitely not going to be able to return a discount because the dimension one column by four rows is not the same over here this is one row by four columns we get an error but no problem lookup can do this look up value there it is comma look up Vector this is like the lookup array as long as it has four elements comma then result Vector this is like return array in X lookup it has four elements so it doesn't matter if they are vertical or horizontal close parentheses and enter lookup does this just fine now the second amazing use for lookup is on the the sheet tax now on this sheet we have an IRS payroll tax table and our goal is to calculate federal income tax withholding for a particular paycheck we actually have to look this value up and using exact match or next smaller or in the old days we called it approximate match lookup we have to look through the First Column find a match and this huge formula at the end from this IRS table tells us how to calculate this tax that means we have to take the cumulative tax paid from previous categories that's that amount that's one lookup Value Plus taxable pay minus pay already taxed from the fourth column times the tax rate from the third column that means we have one two three different lookups in a single formula now if you use x lookup it can totally do it but the problem is you have to use is one two three skip over the fourth argument and five arguments in each X lookup If instead of using X lookup we use the lookup function the formula becomes significantly less complicated and easier to create now here's the algorithm we're going to use for calculating the tax and let's just see if we can look up the first value which we get a match here and it's in the fifth column let's just see if we could use lookup to get that one value equals lookup we're not using the first option we're using the second option well there's the lookup value comma and here's the amazing thing about this second option when we have array if I highlight the First Column which has to be sorted to get this match but if I highlight the First Column and only give lookup three columns it automatically takes the item from the last column it cannot take an item from the middle then if I highlight five it'll automatically get the item from the fifth column and for the fourth column if I highlight four it automatically gets that value so our goal is to highlight the full table and that's our formula remember lookup only does approximate match lookup and it always takes the value from the last column close parentheses control enter and sure enough we got the right value F2 then this algorithm says plus and then in paren parentheses we have to take the taxable pay and subtract from it pay already tax well we can just use lookup find a match and get the item from the fourth column look up we're looking that up comma and highlight I'm only putting one table in I don't have to put a column number like in vlookup I don't have to put multiple arguments like X lookup that's it if I highlight this in F9 you can see sure enough we got the correct value control Z now we have to close parentheses times and then we need from the third column the tax rate look up there's the item to match I'm just highlighting three columns because I want the item from the last column close parentheses control enter and F2 that's a lot easier and less complicated than this is and the reason we can use this and be confident is because in a tax table like this it's always sorted if for some reason we still wanted to do approximate match lookup or in the newer function it's called exact match or next smaller if we wanted to do that and it wasn't sorted correctly then we'd have to use xlookup bought one the First Column of the lookup table is sorted and you're doing approximate match lookup it's much easier to use the original lookup function look up all right now we want to look at our last worksheet lookup formula example let's go over to the sheet table now sometimes we do not have just one lookup table we have products price and units and for each different product I need to use a different lookup table this one it's for Bellon this one's for Quad this is for any product that's not quad or Bellon that means we're actually looking up tables so in each row in each one of these cells we need to look at the product name so in the lookup formula we're going to create in the first row I need to look up Bellon and return this table as a lookup table when I'm in row number two since Aspen isn't quadrillon I need to return this table when I get down to quad I have to return this table now these are all going to be approximate match lookup where we look up units and give the customer a discount so there will actually be two parts to our formula but the first part is looking up a table so let's see how to do that and by the way it's so much easier in Microsoft 365 Excel because we now have the amazing switch lookup function now the EXP expression that's the item in each row that tells us which one of the tables we want to return so for expression it's a relative cell reference here it'll know Bellon down here it'll know Aspen and so on then we type a comma and in value one we have to put the word Bellin that's so it can try and match Bellon when it sees it for the row we have to lock this with the F4 key because we're going to copy this formula down then if expression is valid comma you put the result one so that'll be this lookup table F4 so right now we have we're looking at the product in this row if it's Bellin then put this lookup table then we type comma and notice it says default or the next value and next result default for us will be the final table so right now we're going to put value 2 that's the quad F4 comma and the result if we find a quad in this row this table F4 now when we type a comma Now we use default once we use default then we don't have to use these value 3 and result 3 argument highlight F4 close parentheses here's how this works it's looking at whatever the product in this row is if it's a Bellon it's putting that in if it's a quad here's the lookup table and if it's not either Bell in our quad it's putting that in now this is returning a table so watch what happens when I control enter well for the first row did it get the right table sure enough now when I copy it down we're going to get a spill error but we'll see the next table so for the second row it's not Bellinger quad so it better get the default and sure enough it did if we copy it down one two there's the quad table just as we have here control z z and in the top cell F2 so now we have a formula element the switch function that Returns the correct table for each row now after the equal sign we use lookup we're trying to look up the unit sold the higher the units the bigger the discount that's the lookup value comma and switch is going to be our array or our lookup table for each new row we'll have a new lookup table close parentheses control enter double click and send it down I go to the last cell and hit F2 I'm verifying are the relative cell references in absolute cell references working you betcha they are so switch is a great new lookup function when you have different lookup tables now Escape here's a different situation what if you set up your lookup table like this well that means I need to look up Bell in here down here I need to look up quad well anytime you have a lookup value and there's duplicates or multiple matches and you need to return multiple records the perfect function of course is filter now the array that's only going to be the first two lookup tables so Bell and quad F4 notice that six rows so comma and include we need an array of trues and falses six rows tall so we say hey that six row tall column right there F4 are any of you equal to in each row the particular product as a relative cell reference now right now let's check out what this does close parentheses control enter well for Belling it got it right when I copy it down a row though calc means it filtered to nothing control Z but F2 the third argument in filter if empty that's where we can put our default lookup table so I highlight F4 control enter and if I copy it down one row sure enough it got the default control Z so F2 filter Returns the correct lookup table for every row so after the equal sign look up looking up unit sold as a relative cell reference comma and there it is array is the filter function close parentheses control enter double click and send it down and in the last cell where F2 in it to see if the relative cell references and lock cell references are working and they are so if we have a structure like this you can use lookup and filter if we have Separate Tables switch and look up now we did approximate match lookup if you were doing exact match instead of look up you'd have to use x lookup or vlookup now the old school method if we scroll over here well F2 we just use the IF function now there's a bunch of different ways to look up tables but old school if switch and filter are the way to go if you're curious here's some other examples down here all right next we want to go talk about lookup formulas in power query and Dax now on this sheet we actually want to remind ourselves about a worksheet spill formula and table formula and then we're going to use one two three tables inside of power query and inside the data model using Dax formulas now here's the price lookup table so if we want to spill a formula well we can use x lookup we're looking up the product I want to look up the whole column at once so Ctrl shift down arrow comma lookup array I need to make a match in this table column comma return array I need to return price and that's it we're doing exact match which is the default so close parentheses and spill table formula is similar X lookup we need a relative cell reference using the field name in square brackets and that at symbol the implicit intersection operator which just says hey I need to get a relative cell reference comma match here comma return here exact matches the default and of course in an Excel table that automatically copies down to every row now we want to talk about exact match in power query and we're going to have to use a left outer merge which sounds complicated but it's not and in Dax well we already saw how to do it we use a relationship and related now we'll do both of these right now but when we get to approximate match we're going to have to do something in power query and Dax which is nothing like anything we've done so far now let's start off by doing exact match and we'll do power query in the power query editor window we'll do a left outer merge to do exact match lookup then in the data model we'll see how to do exact match lookup now one two three tables I already import supported them into Power query as a connection only query there's the sales table number one product table number two discount table number three then I took the same three tables using power query I imported them into the data model five is f sales six is product and seven is discount so let's start with power query left out or merge we'll double click 01 sales to open up the power query editor now on the left I can see F sales PQ I can also see D product PQ if I select D product PQ we have a unique identifier here and we have a unique list of product names here's the price remember this is a lookup table or a dimension table over here in F sales we have a product column with many repeats now this should sound familiar because in power pivot or power bi desktop data model we can create relationships this would be the many side the lookup or Dimension table is the one side now in the data model we use relationships to build that connection in power query and in SQL database computer language you use the merge operation or the join operation in power query those are synonyms and the same requirements exist for a relationship you have to have a unique list of items and something you want to look up that's the primary key and over here this is the foreign key with many duplicates so we just say hey product column for each row I need you to go over and get that price now we're going to keep both of these queries as connection only and add the column here to do a merge we start in the fact table with the foreign key this is where we want to add the column with the price we go to home over to combine up to merge queries and we're going to keep the a column in this table so I'm not going to say create a new query I'm going to use merge queries then in the top of the merge dialog box here's the fact table with the foreign key with the many repeats I select it then I come down to the bottom select the lookup or Dimension table D product I select the primary key or the unique list now these two columns are connected just as with a relationship however in power query and SQL we have many join types the join type that we use when we're doing exact match lookup is left outer that means all from the first I want every single Row from here and only matching items from the second so there it is left outer merge click ok now this brings in the entire table with a single matching record all we want is price so you come up to the field name the Del double sideways pointing arrow you click we first come down here and uncheck use original column name as prefix that means we do not want this in the new column name then we uncheck everything and select just the column we want now one thing that's really cool about the merge feature or the join feature is if we were going to look up multiple items we would just check them all and it would add all those columns here we want only price click OK and there's our exact match lookup we have merge and expand two steps to do an exact match lookup now this is connection only so when I close and load it'll just keep it up in the power query editor if we double click and open it sure enough there's our exact match lookup close now I've already added the three XL tables to the data model one two three using power query data data tools and let's click manage data model by button this opens up our power pivot for Excel Window we can see the tables down here over in diagram view we have to have that one that's the unique list or the primary key to many that's the foreign key or the column with many repeats we have to have this relationship first then we go over to F sales data View F sales we could add a new column double click we'll call this price and enter then up in the formula bar because there's a relationship we use the related function and all we have to put in related is the name of the column that has the thing I want to go and get and bring back to this fact table so we'll use price close parentheses and enter so for exact match lookup in the data model using Dax you first have to have this relationship and then you use related now I'm going to control s and close this now we need to talk about approximate match lookup or exact match or next smaller lookup in power query and in Dax now the thing is there is no built-in function to do this so in that way we're Super Lucky in the worksheet to have a function like xlookup but the name of this type of lookup in the X lookup function exact match or next smaller gives us a hint of how we're going to have to do it in Dax and M code and power query now here's the concept of what we're going to have to do in Dax and M code for each row we're going to have to take the lookup value so for this row it'll be 174 and then we have to ask a logical question of each item in the column are you less than or equal to 174 we get one two three trues this is a false that means with these three trues we can filter the discount column to get just these three values and then once we have those values we'll say hey give me the max value now before we jump over to Jackson power query let's see if we can do this in the Excel worksheet well of course we can because we have a filter function my goal is to filter the discount column comma include well I have to ask the question of all the units so we have that column which of you are less than or equal to this as a relative cell reference now close parentheses when I hit enter this will spill if I were to copy it down to the next row we'll get a spill error but notice for 212 we're getting all four values because of course all four values are less than or equal to 212. if we ask the max of all of these it will get the correct discount of 0.5 Ctrl Z so that means around this F2 we just have to put Max close parentheses control enter double click and send it down go to the last cell and hit F2 all the references are working now of course we would never do it this way in the worksheet but this is how we're going to have to do it over in Dax and M code now in Dax we definitely will have a filter function and a Max function in power query M code instead of filter will have a function called table dot select rows and for Max we'll have a function called list dot Max all right let's start off with Dax data data tools manage data model now two important things if we want to do approximate Max lookup over in diagram view the first thing is an approximate match lookup table like this is called a disconnected table because it's hard to make a relationship from one item here over to here because we don't have exact match the other thing is we would like the max function to have a filtered range but there's no filter context in a calculated column that's where the additional function calculate besides filter and Max that function can create filter context in a calculated column so we're going to call this discount enter and then up in the formula bar let's just see what happens if we use the max function and Max needs to look at the disconnected table discount and we're going to use the discount column close parentheses and enter it always is going to get the last value because we haven't filtered it in order to filter this Dax formula in a calculated column that's where the calculate function comes in calculate has some expression and then when you put some filter this function doesn't care if there's not a relationship because whatever filter we put here will filter that column inside of Max so comma and we're going to use the same type of formula as we did in the worksheet using filter the table argument needs the table we need to filter which is disconnected discount this argument is similar to array in the filter worksheet function and we put the entire disconnected table there it is right there comma filter expression is like allow in the filter worksheet function so we say disconnected units how many of you are less than or equal to and guess what we need to access this column here and in a calculated column we have row context which allows the calculated column to see each item in each row so in power pivot we can select the column remember over in power bi desktop we can't we have to type it out but there you go close parentheses on filter and yes it looks a little bit different but filter there's the table there's the filter for that table and now because calculate has the expression and a filter it'll make these two functions work together in every cell in this calculated column when I hit enter that's the crazy way we have to do approximate match lookup with Dax formulas in a column in the data model now I'm going to control s close this and now we're going to see how to do this in power query now in the data ribbon tab query amazing connection let's open up queries and connections pane over here our goal in the F sales PQ table is to do approximate match lookup to get a discount from the disconnected discount PQ table now these are connection only queries and before we go here and do our approximate match lookup I want to go look at the function solution so I'm going to double click lookup solution here's all of our queries on the left this is where I created a formula this is the fact table this is the column where I created the row by row formula and that formula does approximate match lookup to lookup units over in discount and bring back the discount now here's the step for the formula if I click the gear icon you can see the formula is similar to Dax we use table dot select rows and then we do The Logical test units from the discount table are you less than or equal to units over here in the fact table now we're we're not going to build this formula here because this formula is inefficient and the reason why is because we have to pull this query there it is right there we have to pull it for every single Row in this fact table now whereas in the Excel worksheet and the Dax formula engines those formula engines can handle this kind of formula power query M code formula engine cannot handle this type of formula without really going slow but there it is that's the formula it would work on a small data set we're going to do it A Better Way click OK all right this is going to be the strangest lookup process we've done in the whole video let's go back over to F sales PQ here's the fact table over here is Discount we're going to use the append feature now if we look at discount a pen just takes two tables and if the field names are the same they just stack them one on top of the other over here the problem for us is we need units on top of units and this is not the same name so I'm going to rename it just units enter if you need to keep the name then at the end you can change it back now we go up to Home combine append queries a pen and the table we want to put on the bottom discount PQ click click ok now discount did not exist in the fact table so we get this new column and it's all nulls because there was nothing for each row in the fact table but here at the bottom we get the units attached to the bottom a bunch of nulls and discount and here's the trick we're going to sort this field here it'll bring the zero up to the top and the discount the 44 up to the top of any numbers that are 44 or below bringing the discount and so on so we come up to units and we're going to sort a sending a to z smallest to biggest that brings the zero up to the top with nulls and the discount and then all of the records that are zero or less up to but not including the next lower limit for the next discount category those are all the records here's the lower limit of the next category nulls discount all the way down to the next lower limit now we sorted ascending here but within this sort we also need to sort discount descending and we can see that we did have a zero record at the top and by sorting this column within this column that'll bring the zero up to the top so we click the drop down sort descending and up in the formula bar you can see table dot sort there's the major sort and then within that we sort discount and so now we just need to fill down the discounts and sure enough that is a feature fill fill down and there we have what we want our discounts for every row we still have one last step because we need to get rid of the records for the original discount table for a product we'll just click filter uncheck null click OK and now with these five steps we've added doing approximate Max lookup the discount now if in fact you needed units as units sold we could just rename it at the end we probably also need to change the data type here to decimal all right as we can see in power query and in Dax if you're doing approximate match lookup it's a lot harder than in the worksheet all right I've already loaded all these as a connection only so I just click close And load all right that's it that was a lot of amazing fun with lookup and don't forget at the end of this workbook there's homework or practice problems well that was an epic video with 28 examples of lookup lots of Excel worksheet functions including mostly X lookup and some power query and dax2 all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos that come from Excel is fun all right we'll see you next next video [Music] foreign [Music]

Channel: ExcelIsFun

Views: 38,860

Keywords: Highline College, Busn 218, Mike Girvin, excelisfun, Mike excelisfun Girvin, Microsoft 365 Excel, MECS 08, Microsoft 365 Excel Complete Story, Excel Lookup Formulas, VLOOKUP function, XLOOKUP function, INDEX function, Match function, LOOKUP function, SWITCH function, How to lookup record. Multiple Lookup Tables?, Power Query Approximate Match Lookup, DAX Approximate Match Lookup, Most Important Excel Lookup Functions, Functions and Formulas, LOOKUP function beats XLOOKUP

Id: V0vvKFC1wc4

Length: 78min 50sec (4730 seconds)

Published: Thu Oct 20 2022