VIDEO: Mastering Excel XLOOKUP: 🖥️ Never Vlookup again!

You’ve might’ve heard of Vlookup if you’ve been working with Excel or Googlesheets, but you might not have heard of Xlookup! As the name suggests, these formulas help you lookup information within your data and are especially useful for automatically parsing out huge chunks of data. Xlookup is basically vlookup’s bigger brother, and was only introduced in 2019 for Excel and 2022 for Google Sheets. As the resident Excel nerd on the team, I think it’s way simpler and easier to use instead of vlookup.

It looks like THIS. The lookup value is what you’re looking for. The lookup array is where you’re looking. And the return array is what you want the system to spit back out. Let’s say I’m looking in this big list of data, and need to find the e-mail that belongs to Bob. I’ve got this column of emails, a bunch of data I don’t need for this task, and then a column full of names. So, I could go into an unused cell and say =xlookup(. Lookup value is what I’m looking for, so I’d put in “Bob.” Lookup array is where I’m looking, so I’ll just select the column with all the names.

The return array is the info we want, so since we want Bob’s email, I’ll select the column with the e-mails. And there you have it! Now that’s all you need for the basic functionality of Xlookup. However, you can also use these three more OPTIONAL variables for some fine-tuning. The variables are: If_not_found Match_mode And search_mode If_not_found is a baked in error handler. So, if the lookup_value isn’t found in the lookup_array, it will return whatever you put here.

If you leave it blank, Excel will return with #N/A. Match_mode determines what happens when the system cannot find an exact match. It gives you 4 different options to choose from: 0, which is the default. If an exact match isn’t found, the “if_not_found” variable will be returned instead -1 means if the system can’t find an exact match it’ll return with the next smaller item. 1 means if the system can’t find an exact match, it’ll return with the next larger item.

And 2 is a wildcard match where the user can use asterisks and question marks. This takes a bit to explain, and chances are you’ll want to just stick with the default anyways. Lastly, search_mode gives us another four options that we can choose that specify HOW the system searches. That sounds vague, but I think it helps to know what each of the values are: 1 performs the search by starting at the first item of the lookup_array. This is the default, since typically you want to start at the top -1 performs a search by starting at the last item of the lookup_array, so it goes from the bottom up 2 performs a binary search, and requires that the values in the lookup_array column are sorted in ascending order. -2 is also a binary search, but this one requires that the values in the lookup_array column are instead in descending order.

While all these options are cool, you’ll typically be using the first two options, 1 or -1. So, using my Bob example from earlier, if I wanted the system to look for Bob’s name in the list of names and return their e-mail, but if the system can’t find it I want it to say “nope,” find only an exact match, and start searching from the bottom of the list instead of the top, it would look like this: =xlookup(“bob”,the column with the names, the column with the emails,”nope”,0,-1) If that optional portion went over your head, don’t worry, like I said those last four bits are optional and are only necessary for specific niche instances.

Now, you might be asking “That’s cool and all Kyle but why would I use this instead of Vlookup?” And look, Vlookup is great. For a more in depth look at it, you can check out our step by step tutorial. But Xlookup DOES have two pretty significant advantages over vlookup: The error handling is a lot better since it’s baked into xlookup instead of depending on an additional formula, like =iferror() And Xlookup does not require the lookup_array to be before, or to the left of, the return_array.

This was a huge drawback of vlookup, because in Vlookup you could only look for values to return that were to the right of your lookup value. So you’d often have to rearrange your entire data sheet in order to get vlookup to function the way you wanted, but with Xlookup that’s no longer necessary. There you go, fellow spreadsheet nerds. Now you’ve got one more tool in your Excel and Google Sheet utility belt! Go test it out. I hope you learned something new today!

If you found the video useful, hit those like and subscribe buttons down below, and leave a comment, we’d love to hear from you! Thanks for watching, and I’ll see you next time.