Retrieve data with XLOOKUP & STOP the VLOOKUP madness (2024)

[Music] I promise you vlookup is wasting your time if you find yourself trapped in this mad Loop of learning forgetting and relearning vlookup every single time you have to use it you're in good company because the struggle is so real it's a frustrating function especially because the problem it's supposed to solve it's just super important and comes up all the time but today the madness stops at least for you because you're about to learn xlookup the modern simpler replacement to vlookup I'll guide you through what you need to know but trust me you're going to love xlookup now everything has a purpose first let's understand the job to be done let's take a look at a few movies and I'm curious for you movie fans if you picked up on what these movies have in common all right so here's my question are these movies worth my time one rating system we can use is IMDB ratings okay so this table we just built let's call this our main table here's the thing I've been duped by rating systems in the past and now I don't trust any one single rating that's why I also want to look at Rotten Tomatoes ratings specifically their Tomato Meter that one measures what the professional critics think now in in an Ideal World our data would be neatly organized in one place right where we need it but we have a problem the real world is messy data is usually scattered across various tables like our Tomato Meter data which is sitting in some other table it becomes our job to retrieve data from where it is to where we really need it take it from an Excel Pro the ability to retrieve data from some other table is one of the most important Excel skills that you can learn it's often what separates beginners from intermediate and advanced users that's the job to be done now xlup makes his job of retrieving data as simple as you can possibly make it still I don't want you falling into another mad Loop where you learn forget and relearn how to write the formula so to understand what xlookup is really doing I spent possibly too much time building visual intuition let's introduce xlookup to our scenario to get the job done xlookup is going to ask three basic questions first what thing are you trying to get data for in our case it's movies listed in our main table and if we start with the the first row that would be Wonder Woman second where should X lookup search for Wonder Woman in our case we'll search in our other table remember that's where our Tomato Meter ratings are but we want to specifically search for Wonder Woman in the movie title column one by one from top to bottom and once xup finds a match it stops searching now quick heads up in a moment I'm going to share two really important things you need to know about matching but for now let's move on to the third question once they finds a match for Wonder Woman xlup is going to ask you for the corresponding column that has the data you want to retrieve we want data from the Tomato Meter column and at last xlookup brings back Wonder Woman's Tomato Meter rating did you catch that logic just tell xlookup what movie do you need data for where xlookup should search for that movie to find a match and last which column has the corresponding data we want to retrieve with these three steps in mind let's watch x lookup repeat repat the process for Casino Royale and Lion King what movie where to search for that movie and what to finds a match what's the data to retrieve boom that's how the job gets done now if I've lost your attention even a little bit Now's the Time to bring it back because there are two things you need to know about matching first X lookup searches for an exact match so if the data has a typo like we have here with Wonder Women or if the title's just written differently X lookup will not find a match this idea of looking for an exact match isn't specific to xook up or even Excel for that matter rather it's a fundamental data concept that comes up all the time okay second thing you need to know X lookup retrieves data for the first match and only the first match let's introduce the 2017 liveaction Wonder Woman since both movies have the exact same title xlookup can't tell the difference between the two movies so what it'll do is retrieve only one value and it'll be for the first Wonder Woman advin and it'll ignore all others having two different movies listed under the same name is bad practice but it happens so just be careful keep these two concepts in mind as you're crafting the formula and speaking of formulas you're about to see how the visual intuition you just built perfectly explains how to write an xlookup formula remember to get the job done we have to tell XL up three things what movie where to search what data to retrieve X lookup just makes sense I mean doesn't it Simplicity beg the question why use vlookup at all now understanding how xlookup retrieves data didn't actually require Excel but to truly Master it and to build your confidence practicing Excel is a must of course we'll cover the basic scenario but stay to the end to see how Pros use xook up to handle a more complex but surprisingly common situation like when there are multiple movies with the same title now if you want hands-on experience check the link in the description for the same Excel file I'll be using all right let's get it here we have our two familiar data tables just with a few more movies included so starting in cell B4 let's write equals x lookup open parentheses the movie we want data for so Wonder Woman in cell B4 comma then the second part where to search for Wonder Woman so we want to search the movie titles data in our other table that that's column I which I'll select by clicking on the I then comma and last what data we want to retrieve that would be our Tomato Meter data in column J let's close parenthesis hit enter and boom let's copy that formula all the way down by clicking on the bottom right hand corner of the cell with our formula look how easy that was now check this out xlup has an additional feature I haven't told you about here's an annoying situation imagine we were missing data in our other table that means xlup can't find a match for Wonder Woman and Westside Story that's why we're getting errors in this situation you can give xlookup a fourth direction for what to Output in place of an error for example I can write anything I want like no data which we'll put in quotes since it's text so now xlup outputs no data instead of an error V lookup can't do that congrats with X lookup in your Excel toolkit you have the skills to solve the majority of data retrieval task but you might be surprised to know that you're this close to reaching an advanced level remember earlier when we introduced a liveaction Wonder Woman that shares the same title as the animated movie so xlup won't know which movie to retrieve data for in fact every movie in our data set has been remade but kept the same exact movie title that's a big problem but we can differentiate Wonder Woman release in 2017 from Wonder Woman released in 2009 if we include their year with the movie title there's a few ways to combine cells in Excel speak we call this concatenation the way I'll show you is to combine movie and year using an Amper it's super simple starting in cell be4 we'll add a new column then write equals our movie title Wonder Woman Ampersand then our year 2017 and hit enter copy that formula down now every movie has its own unique identifier something specific to that movie let's do the same for our other table equals 4 Ampersand M4 hit enter copy that down and now we can easily write our xlup formula starting in F4 we'll write equals x lookup open parentheses now rather than selecting just wonder woman we'll select the combined movie and year in B4 comma search for Wonder Woman 2017 in column K and last retrieve data from column n and we get our correct rating for Wonder Woman released in 2017 let's copy that down and boom the job is done hey thanks for sticking around we're a brand new channel so if you found this video valuable we'd love your support with the like or a comment and if you're not subscribed don't be shy come back and check out another video with that I'm Mario Strada and this is hot sheets don't tell your boss

Channel: Hot Sheets

Views: 3,828

Keywords:

Id: 1JC9axbDBjY

Length: 8min 29sec (509 seconds)

Published: Tue Mar 12 2024