XLOOKUP for Excel Explained in 3 Minutes

The Opinion Lighthouse
32 followers
15 Video Views·Nov 8, 2022
XLOOKUP for Excel Explained in 3 Minutes
Welcome to ExcelCampus. My name is John, and in this video I'm going to give a brief overview of the XLOOKUP function.
XLOOKUP is a new function in Excel that Microsoft is calling the successor to VLOOKUP and INDEXMATCH.
The goal of XLOOKUP is to look for a value in a column or row, and return a value from a cell in a matching row or column.
XLOOKUP has five arguments, but only the first three are required.
Those three arguments are the lookup value, the lookup array, or the column or row to look in,
and the return array, or the column or row to return a value from.
In this example, XLOOKUP is looking for the name in column E and returning the email address from column G.
This makes for a simple formula that performs an EXACTMATCH lookup.
One advantage over VLOOKUP is the separate ranges for the lookup and return arrays.
This is similar to an INDEXMATCH formula and prevents errors when columns are inserted or deleted.
Another advantage of XLOOKUP is that it returns an EXACTMATCH by default.
This means we do not need to specify anything for the fourth argument, MATCHMODE, if we want to do an EXACTMATCH.
It also allows us to return a value from a column to the left or row above, something VLOOKUP can't do on its own.
XLOOKUP can also do horizontal lookups by looking through rows. This is similar to HLOOKUP.
The fourth argument, MATCHMODE, has options for EXACTMATCH, which again is the default.
It can also do CLOSESTMATCH with the second and third options, which is great for tax and commission rate calculations.
The fourth option, WILDCARDCHARACTERMATCH, allows us to do partial matches with wildcard characters in the lookup value.
In this example, I'm using the question mark as a wildcard character to find a match where this name might be spelled with a Y or an I for the third character.
Check out my other video on XLOOKUP for a more detailed explanation on wildcard characters.
The fifth argument, SEARCHMODE, allows us to specify the search order.
This is something VLOOKUP and INDEXMATCH can't do without sorting the data first.
By default, XLOOKUP will search first to last, either top to bottom or left to right.
However, we can specify a negative 1 to reverse the order and search from last to first.
It's great when you want to find the last occurrence of an item.
In this example, I'm finding the last sale made on a specific date.
There are a few potential drawbacks and things to be aware of with XLOOKUP.
If the lookup and return arrays are not the same length, you will get a pound value error.
It's just good to know this as it will probably be the most common error we see with XLOOKUP besides the pound NA error when a value is not found.
The other major drawback is compatibility.
XLOOKUP is only available for Office 365 subscribers.
At the time of this video, it's on the Insiders Fast channel and will be rolled out to other Office 365 channels in the coming months.
This means you won't want to use XLOOKUP if you're sending your files to users that are on an older version of Excel.
Cells that contain an XLOOKUP function will return a pound name error when the formula or workbook is recalculated.
Again, this is just something that's good to know if you're sharing your files with other users.
So that's a quick overview of the powerful new XLOOKUP function.
Please check out my other videos on XLOOKUP for more in-depth tutorials.
And leave a comment below with any questions or suggestions.
Thanks again for watching, have a great day, and I'll see you in the next video.
