When to use VLOOKUP vs XLOOKUP vs INDEX MATCH?

X lookup vlookup index match which one should you actually use to answer this question I created a simple diagram here all it's saying is that if you have Excel 2021 or Excel for Microsoft 365 so basically any of the newer versions of excel you should be using X lookup in my opinion and then if you have any of the earlier versions of excel then you have a choice if you're looking up to the right of the value then I think you should be using vlookup and then if you're looking up to the left of the value then I think you should be using index match now don't worry if you don't know these functions now as obviously in the video I'm going to go through everything in detail and hopefully by the end you'll be an expert on X lookup vlookup and index match so let's get to it let's start with vlookup because it is the simplest out of these three functions and it is the one you should be using if you have an Excel version that is pre-exl2021 so basically anything other than Excel 2021 or Excel for Microsoft 365 then you should be using vlookup to look up to the right of the value so just to quickly go through the data that we'll be using we have an orders table with some order IDs customer names customer IDs emails and the sales amount and then to the right of this we have a table where we'll be looking up the email the sales the customer name the order ID based on the customer ID here so let's quickly get to the function we can start typing vlookup and Excel will give us recommendations based on what we start typing you can hit tab to Auto populate this function for you and then Excel will give you the arguments for this function so we can see that this is the lookup value the table array the column index number and the range of the lookup so let's go through these so the lookup value is the value that you want to be looking up in our case that would be the customer ID so let's click on customer ID we selected cell G4 the next one we are going to select is the table array so where do we actually want to look up this value from and remember that with vlookup you can only look up to the right of the value so in our case we are going to select this range here starting from customer ID going all the way to the bottom of sales and make sure to hit F4 to lock the cells in so the formula will always look up from this range and next one is the column index number which essentially is just asking you from this range here which column do you want to return the values from so customer ID would be column number one email would be column number two and then sales would be column number three so because we want the email that's called number two and then moving on do we want an approximate match or do we want an exact match so I want an exact match so I'll type in false and then I'm going to hit enter and then here we go we can see that we got the email address back T Benedict tovich MV at ebay.com we can quickly double check this value here so I can see that this value is here the customer ID tag to it is five three eight six four three six two one FG and 53864-36201 FG so it's the same values you can Auto populate this function by double clicking at the right bottom here when you see this little cross up here you can double click and it'll Auto populate the function for you or and I'm going to hit Ctrl Z now to undo or you can grab it and drag it down which will also do the same and I am going to hit Ctrl Z again or you can hold down shift press down arrow all the way you get to the bottom and then you press Ctrl and D for down and this will also Auto populate the function for you so numerous ways just pick whichever one you feel comfortable with moving on to sales we're going to be doing the exact same thing but not with email with sales so let's start typing the vlookup function we can hit tab to Auto populate what's the value we want to be looking up again this is the customer ID so let's hit G4 the cell and then table array remains exactly the same so starting with customer ID column all the way to sales the bottom of the sales make sure to press F4 to lock in the table array and then moving on to the column index number so if email was called number two then sales must be column number three and here we go and the range of the lookup again we want them exact match so I'm gonna pass in false and hit enter and there we go we got the sales amount back and we can just Auto populate the function here and here we go we have it the next one up is customer name and this is where vlookup has its limitations as it can only look up the values to the right from the customer ID so in this table here based on customer ID using vlookup we can look up email and sales but we cannot look up order ID or customer name and let me show you let's start typing vlookup the lookup value again it's the same the table array this time say I am going to select everything from order ID to the bottom of customer ID hit F4 to lock the cells in and then column index numbers so if customer ID was column number one if we count backwards customer name must be column number zero and Order ID must be column negative one I mean this is incorrect but let's just do it so I am going to pass in 0 for the column index number that we want to retrieve because we want the customer name and then again I want an exact match so I'll pass it pulse and hit enter we can quickly see that this didn't work we can of course try the same with order ID so let's just pass in the customer ID again for the lookup value table array select the exact same thing hit F4 to lock in the cells and for the column index number this time we'll choose negative one to be consistent and then I want an exact match so fast and false hit enter again there we go we can see that this didn't work again so that's where vlookup has its limitations as you can see it can only look up values to the right so email and sales worked but customer name and Order ID didn't this is where index match will come in handy so just to go through the use case of index match essentially if you're looking up values to the left and you have a version of excel that is pre-exl2021 or Excel or Microsoft 365 then I think you should be using index match it is a little bit more complicated than vlookup but let's jump into it so index match the function index match is a combination of obviously two functions index and match to understand how these work together let's first go through match match essentially just returns a number for you so it'll match whatever you want to within a certain row or a certain column and I know this doesn't make too much sense now but as we go through the example I promise you it will so first of all uh what we want to get back is say for example the email or this customer ID now in order to do this we need to know within this table which row this customer ID is in and which column the email can be found so essentially we want to find this field here which is in row one two three and then we want to find email which is in column one two three four so that's all we're doing here with the index match function we just want to select something the value that is in row three here and column four I'm just going to start typing away on the bottom here so let me just copy the custom ID with Ctrl C and Ctrl B down here and let me also copy the email down here just for demonstration purposes so let's first match the row number of this customer ID here so in order to do that we start typing the function match and we can hit tab for excel will populate the function for us and then here we go we have the arguments lookup value lookup array and match type lookup value pretty obvious the customer ID lookup array would be the entire customer ID column here we go and the match type we get less than exact or greater than we want an exact match hit enter and there we go we got back row number three which makes sense because if you look here this is in row one two and three now we go with email so let's match this which column is email in let's see the match function again same arguments here so we have a lookup value lookup array and match type so what are we looking up email where are we looking in the sub from up here these columns so from the single row at the top here and what is the match type again we want an exact match so here we go you see it here we want an exact match so I'll type in zero and we see we got two numbers back so we got Row three and column number four so now we have everything to use the index function so let me just type the index function down here at the bottom again index we start typing you can hit tab to Auto populate and let's just look at the arguments quickly so we have array row number and column number now what's great is that we already have the row number here three we already have the column number four so all we have to pass in now is the array and the array will just be the whole table so select everything from order ID all the way to the bottom of sales and then we can pass in the real number which is three and then we can pass in the column number from up here which is uh four these numbers here and here we go we can close the brackets and hit enter now you see here the value that we got back is T benedictovic MV at ebay.com which let's just double check for this customer ID here yes that is correct you see here essentially this is the same value that we got back using vlookup here just using index math so it is a different way and it is a bit more complicated as you can see but the good thing about it is that it is more dynamic in the sense that you can look up to the right to the left and obviously you can look up dynamically whatever you want so you could look up the customer ID and then the emails the sales you can change these values so say for example if I change this one let's say to sales this email here I can change it to Sales and then this changes to a 5 here so then if we go into the formula we can change this at the top here where my mouse is hovering to five and then I get back to sales and this is where index match is really powerful you can use it dynamically and I am going to show you how by populating this portion of the table here using the one formula let's start by typing the index function you can hit tab to Auto populate it will ask you to select the array first so let's select the whole orders table everything from the order ID to the bottom of the sales as the array hit F4 to lock in the reference cells now moving on to row number and this is where we'll actually use match to return a dynamic row number for us so we don't have to hard code it so start typing match you can hit tab to Auto populate the value for you and then here we go we're presented again with the same arguments lookup value lookup array and match time so what is the lookup value well again the lookup value would be the customer ID and here I am going to press F4 once I am going to press F4 twice and I am going to press F4 one more time we end up with this dollar G4 dollar essentially just locks in that column for us so as we drag the formula to the right this G column will not move it will just stay locked to G4 as we drag this formula across which will be very helpful when we want to make this formula Dynamic and it'll help us in writing only one formula to populate this whole table now moving on to the lookup array which will be customer ID column so select the whole custom ID column again press F4 only once here because you want to lock in the whole column this is where you'll be looking up the customer IDs from all the times and then for match type here you can go exact match which is what I'll do this is the real number so now moving on to the column number let's start typing another match function you can hit tab to Auto populate and then what is the value we're looking at now so now the value we're looking up is the email value and you can hit F4 once and then you can hit F4 twice and this is where you stop so you end up with this H dollar three h dollar three just means that it'll lock in Row 3 for you why is that good as you drag down the formula it'll stay here this cell in h dollar three so rather than going to H4 because of the dollar it'll stay in H3 as you drag down the formula so let's look up the lookup array the lookup array for this will just be the first row here so starting from order ID all the way to sales and you lock this in because you'll always be looking up the values from here and then match type is exact match so you type in a zero then close the bracket here and then obviously close the other bracket as well and hit enter and there we go we got the email back for this customer ID now why is this formula so good why is index match good well because if I drag this formula now to the right there you go you can see that it looked up the email then it looked up the sales then it looked up the customer name which is to the left of the lookup value and then it looked up the order ID so here we go this is where index match is really powerful let's just look at this line here and let's locate it in the orders table it would be Row 5 test benedictovic with this customer ID that we just looked up with the email address that we just returned and the sales amount is 47.55 let's just double check 47.55 here as well and that is why index match is really powerful we can just use this dynamically so if I select all of these cells now and I go to the right bottom here double click I essentially populated this whole table with just one single formula that I wrote in cell H4 and the formula is here let's just look through it again we had index and then within the index we select the table array then within that we have the first match function here which will return the row number for whatever customer ID you have here and then you have the second match function which will return the column number for whatever you want to look up so essentially just your column titles here so just to show you that it is dynamic say for example I change this here to sales then you see these two columns here there exactly the same and I am going to press Ctrl Z to undo but I could change this to customer name and there we go these two columns now are exactly the same that's why index match again really powerful it's Dynamic so it can look up to the right to the left if you know index match you know one of the bread and butter things that Excel has to offer which is not a bad thing I guess and last but not least X lookup you should be using this one if you have Excel 2021 or later so if you have Excel 2021 or Excel for Microsoft 365 I think you should just use xlookup because it's that easy to use it's a lot simpler than index match it can look up to the left and to the right let's go into email here start typing the function again we can hit tab to Auto populate and let's check the arguments quickly so we have the lookup value the lookup array the return array then we have an if not bound build which is optional match mode again optional and then you have the search mode which is again optional so what are we doing here again we're looking up the email for this customer ID so let's look up this customer ID the lookup array you can just select the customer ID column this is where you'll be looking up the value from Hit F4 to lock in the cells the return array is where you want to be looking up the values from so this would be the email column selected and then hit F4 now the if not found is just an optional argument we can leave it blank for now for the purposes of this video and the match mode we want an exact match so I think it defaults to zero anyway but I'll just type it in and for search mode we're not going to be bothered about that for now we'll just pass it in close the brackets and here we go we have the email returned for this customer ID and let's just make sure that it is the right email um yeah let's just uh and just type in email here again yes the benedictovich and be at ebay.com I feel like I memorized this by part by now so here we go we have this function populated for us again you can double click or you can drag it down to Auto populate the function for you now moving on to the next one sales rather actually I'll just show you one example where we look up to the left rather than looking up to the right so we'll find say for example the order ID that belongs to this customer ID here let's go into the order ID column here start typing our function X lookup and then hit Tab and we'll look up the customer ID the lookup array again is the customer ID column there we go hit F4 to lock in the value the return array this time will be the order ID column so we're looking up to the left hit F4 to lock in the cells if not found we'll leave it a blank and then match mode just to show you that if we don't pass in anything it will actually just default to zero the exact match I'll leave it blank close the brackets and hit enter here we go we got the order ID back for this customer ID so let's just double check ABK and it ends in five three one so let's double check here we have ABK 531 and it's definitely this customer ID because I can see that it's the D benedicto which MB at ebay.com email here for the purposes of completeness let's just come here and drag this down and here we go we have the order ID populated as well so you can see that we use the X lookup here to look up to the right when we looked up email so you can see it here that customer ID is here email is to the right of customer ID but then at the same time when we looked up the order ID column here we actually looked up to the left of customer ID so we can see that customer ID is here and Order ID is here to the left of customer ID so X lookup really just provides you an easy way to look up to the left and for the ride but it only works on Excel 2021 or Excel for Microsoft 365. if you like this video and you would like to see more content like this then stop to my channel would be great thanks again for watching and I'll see you in the next one [Music]

Channel: Mo Chen

Views: 27,347

Keywords:

Id: n0vqkuRyMvQ

Length: 19min 26sec (1166 seconds)

Published: Sat Jan 14 2023