An XLOOKUP is one of the latest lookup functions available to Excel users with Microsoft 365. If you're currently using VLOOKUPs or INDEX/MATCH in your day to day, then you'll definitely want to keep reading because the XLOOKUP is a gamechanger!

You can use the XLOOKUP function to find items by row in a table or range. For example, look up the title of a product by the part number. With XLOOKUP, you can search in one column and show a result from the same row in another column, regardless of which column contains the return value. 

Let's dive straight in and see the XLOOKUP in action!


As you can see the XLOOKUP function is made up of 3 required arguments, and a further 3 optional arguments. Optional arguments are shown in excel using the [ ] brackets. In this quick run through we will look at the 3 required arguments, AND the ✨[if not found]✨ argument.

In the example we have a list of products, some product details and online availability. And we need to use this data to fill the gaps in our table of best selling codes.

But as you can see we only have the Title for the bestsellers, but need to populate the code and the online availability columns. Which we can do with an XLOOKUP. Note: this can be done across different worksheets or books, but for illustration purposes I'm working all in 1 sheet. 

Let's start with the Available Online Column, the function we need to use looks like this: =XLOOKUP(I4,C:C,F:F,"Missing Data")

I4 = The value we are looking for, in this case the Title we have.
C:C = The column in our source data where I4 will find a match, the Product Title column.
F:F = Our desired output column, the Available Online column in this example.
"Missing Data" = Here is where we can get fancy and tell Excel what the put if it can't find a match in our source data. This can be a cell reference, text, a formula or number. In our case I'd just like it to return the text "Missing Data".


Now we can drag this down or double click on the bottom right corner of J4 to perform this function for all required products. 

What really sets the XLOOKUP apart from it's older siblings the V or H lookups, is that I can now repeat this process for the Code Column, even though my output column comes BEFORE my lookup array column! Let me show you what I mean...


Hopefully this gives you a taste of the ways you can use XLOOKUP in your day to day to help save you time and potential errors! Say goodbye to counting those columns 👋

 

Jordan 📅