First, I apologize for the opening sentence to this video. It's been almost 4 years since my last Excel tutorial. At the time, I covered all I wanted but the introduction of XLOOKUP in Excel deemed it necessary for me to create another tutorial. 

Today's tutorial covers the XLOOKUP. It was introduced in August 2019 to only Office 365 subscribers. Users who had the monthly subsription was able to enjoy the feature months ago. However, if you're on the semi-annual membership, you should be able to use it in your local version in July 2020. I was able to create this tutorial because I have access to an Office 365 instance of Microsoft Excel. Unfortunately, this will not be available in older versions of Excel. 

So what is XLOOKUP? Previous, I covered VLOOKUP, HLOOKUP, and INDEX/MATCH which are all lookup functions. VLOOKUP and HLOOKUP allowed you to ONLY search data from left to right. Meaning that if you wanted to find a value for a name, the name MUST be to the left. 

The INDEX/MATCH formula offered some flexibility beacuse it allowed you to search right to left as well. It used two different fromulas and looked a little clunky and it scared more people than it should have. It was my favorite lookup method until now. The XLOOKUP comes to save the day. 

What does the formula look like?

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

It's pretty simple and it has 3 required arguments and 3 optional ones. The required arguments are:

Arg	Description
lookup_value	The item you are looking up
lookup_array	The list of items you are comparing the lookup_value against
return_array	The list of items from which the value that will be returned to your formula

The optional arguments are:

Type	Example
if_not_found	What should it say if it's not found? Default is '#N/A'
match_mode	0: Exact match (Default)  1: Approximate Match - returns the next largest item -1: Approximate Match - returns the next smallest item  2: Wildcard match
search_mode	1: Default search. Searches Top to bottom and returns first match. -1: Reverse search. Searches from bottom to top and returns first match  2: Binary Search - lookup array must be sorded in ascending order -2: Binary search - lookup array must be sorted in descending order

I didn't go into what a binary search is in the tutorial, there are a ton of resources explaining what it is. A great place to start would be this Wikipedia article: https://en.wikipedia.org/wiki/Binary_search_algorithm

An example of the full XLOOKUP formula looks like this:

=XLOOKUP(F2,$B$2:$B$20,$C$2:$C$20,"",0,1)

F2 is the value we are looking up and comparing against the values in B2:B20. We are then extracting results from C2:C20 and placing a blank cell if it's not found. Blank cells are much better to look at than "#N/A" don't you think?

We kept the default match mode as 0 because I was looking for an exact search. And my search mode is normal where I'm looking through an unsorted list from top to bottom. If there's enough interest in the match_mode and search_mode options, I'll create another video. But for now, I thought I'd focus on something to get everyone started really quickly. 

Have questions about the tutorial? Ask below or on YouTube! Thanks for watching!

Remember to checkout the Resources section below for associated downloadable content, JSFiddle links, and other resources. Watch the video and follow along!