- [Instructor] This next new feature here in Excel is getting a lot of buzz. It's actually a new function that replaces a couple of very popular functions for finding and retrieving data. I'm talking about XLOOKUP. Many people will use Excel and Sheets to store information almost like a database. Here we have a vendor list with rows and rows of data. You can see multiple columns. And when you need to find and retrieve parts of that data it could be very time consuming, but typically in the past, people would use a function like VLOOKUP, which was very good at finding and retrieving information, but it did have its limitations. If you were searching horizontally, you would use HLOOKUP. Well now, we can use XLOOKUP to replace both of those functions. So let's go to our top contacts here. You can see I'm looking for the vendor for a contact that appears on the vendor list. So here in the vendor cell next to Jennifer Peterson is where I start my XLOOKUP function with an equal sign. Then we type in XLOOKUP. You can see it's already starting to show up. Put in the opening round bracket or parenthesis, and you can see we get a little hint here showing up. Lookup value is highlighted. So the value that we're looking up is Jennifer Peterson. We simply click that cell and B6 is entered for us. And you can see the first variable shows up. Now it's time for the comma to move on to the second variable, the lookup array. So Jennifer Peterson, as I mentioned, appears on a different sheet in a contacts column. So we go to that sheet by clicking vendor list. And then clicking at the top of the column for our contacts where Jennifer appears. That's column B. Now we can type in the comma, and you can see even though we switched sheets here to the vendor list, we still see our little reminder here, or hint popping up. The next thing is the return array. What is it we wanted? The vendor name. That's in column A. Notice we're going left here of the contact. Something you couldn't do with VLOOKUP. You'd actually have to rearrange your columns if you wanted to be able to do this. That can be a little bit time consuming and requires some effort. Now all we do is click at the top of column A. You can put in the closing round bracket if you want, but you don't need to, you can just hit Enter here. It'll take us back to our top contacts sheet. And you can see a vendor is found and retrieved for Jennifer Peterson from our vendor list, and it shows up here in our vendor cell. Click it and you'll see the entire function with all its variables. Cool thing is now, for the rest of them, all we have to do is copy this function down by going to the bottom right hand corner handle, click and drag straight down to next to Roger Fuller here, and there's all the vendor names just like that. So in that case, you can see we didn't have to know columns and cells. We just simply clicked and we were able to search left as opposed to right in the column A set of data, rather than moving to the right only as we would have with VLOOKUP. Let's try it again for the email address. It's equals XLOOKUP here's the opening round bracket. Again we use the name, Jennifer Peterson, a comma. Now, all we have to do is find her email address, it's going to go in here, by going to the vendor list, selecting the contact column where Jennifer appears, a comma, this time we're going to the right. That's fine. Click column D, that's the email addresses, and press Enter. There's her email address. And sure enough, it matches up. And again, we'll click in the cell itself, go to the handle, the bottom right hand corner, click and drag it down to get all the rest showing up. Click in the background to see what that looks like. So you no longer need VLOOKUP and HLOOKUP. XLOOKUP will cover them both. And it's much easier to use than those other two ever were. Yet, still just as fast and accurate.
- Getting writing assistance from Editor
- Using the Resume Assistant in Word
- Turning data into maps in Excel
- Setting access to linked files right within Outlook
- Creating better PowerPoint lists with Designer
- Using the math assistant in OneNote
- Working with Microsoft To-Do and Forms