What can you do with XLOOKUP in Excel?

- [Instructor] What exactly is XLOOKUP and what can you do with it? That's what we're going to answer in this video. There's XLOOKUP and VLOOKUP. VLOOKUP is the older, clunkier counterpart of the newer XLOOKUP. As of right now, XLOOKUP is only available to you if you're an Office 365 subscriber. I'm going to be using that in this example. In its simplest form, XLOOKUP will look up a value, in this case, the state, and return another value that it finds in that same row. For example, the status of Alabama is active. The key where we want to return the data is the cell that will contain the XLOOKUP formula. A great thing about this is that we can change the data right on the fly. For example, I'll type in another state. As soon as I hit the Enter key, it looks it up again and the status changes. Here's Colorado and it is indeed inactive. Once you become a little bit more comfortable with XLOOKUP, you can start doing some really neat things. You can even start fixing data on other spreadsheets. For example, here's a spreadsheet that contains a first name, last name, an address. However, it's missing the phone number. I'd really like to have that in there. Further down in the sheet, I can see some matching addresses and here's the phone numbers. We can use XLOOKUP to look up this address and find the corresponding phone number. Now, I already have that formula written because I'm just showing you how it works and what you can do with it. So I'll go ahead and paste that in. And sure enough, it found the phone number. In fact, this XLOOKUP formula is a little bit more sophisticated because this one is using address and phone as easy references because they're named ranges. Something else that you can do in Excel to make life easier is you can even populate this entire column. Remember, in Excel, I can place my cursor on the bottom right-hand side of this cell and drag it all the way down and it's going to fill those values. So because it's always looking at the address field of each individual row, it's going to find the correct phone and match it up. So that's a little bit of what you can do with XLOOKUP. It's fun. Initially, XLOOKUP can look very overwhelming and hard to figure out what kind of data Excel is looking for. I'm going to show you all of that. And once you can learn the inner workings of it, you'll see that it's really easy, fun, and quick to use.