Let’s assume that you have a list of elements in Microsoft Excel. You need to go through all of them to find specific terms, and when you find them, you want to return a matching result. All of this can be summed up with an Excel Xlookup formula.
In this article, you’ll find:
- What is an Xlookup in Excel?
- Microsoft Excel: Xlookup formula
- Microsoft Excel: Xlookup Optional Parameters
- Xlookup Excel: If Not Found Option (4th Element)
- Xlookup Excel: Match Type (5th Element)
- Xlookup Excel: Search Direction (6th Element)
- Microsoft Excel: Xlookup Example
What is an Xlookup in Excel?
The Xlookup is one of the newest functions offered by Microsoft Excel and allows you to obtain matching results after looking for some elements in a list.
The Xlookup can be very handy and make you save a lot of time.
Microsoft Excel: Xlookup formula
When you use the Xlookup, you have to remember that there are three values that should be added to the formula to make it work and 3 optional values.
The three mandatory values are:
- The element we’re going to look for
- The list in which we want to look for the element above
- The matching results we want to get in case the first element is found.
These three elements are crucial for the Xlookup, make sure to add all of them to the formula to make it work properly.
=XLOOKUP(Element you’re looking for, lookup list, matching results list)
The one above is an example of a standard Xlookup formula that contains only the three specific elements, crucial to make the formula work.
As we’ve already said, Microsoft Excel gives you the opportunity to add more parameters. Let’s go through all of them, one by one.
Xlookup Excel: If Not Found Option (4th Element)
The If Not Found option gives you the opportunity to add some specific values, words or numbers to cells whenever our first element is not found.
As we said, the If Not Found option is optional, but might be useful to highlight that the result was not found.
Xlookup Excel: Match Type (5th Element)
For the fifth parameter, which is optional, Excel gives you the opportunity to select a match type.
Add the value 0 to the formula, if you’re going to look for an exact match.
Add the value -1 to the formula, if you’re looking for a match that can be equal to the next smaller item than the exact match.
Add the value 1 to the formula, if you want to look for an exact match or the next larger value.
Add the value 2 to the formula, if you’re looking for a wildcard exact match. The wildcard exact match is a very interesting option that gives you the opportunity to look for values that are partially included into the list of elements. Use characters such as * or ? in your first parameters to trigger the wildcard character match. However, make sure to practice, as this function can be a little tricky.
Xlookup Excel: Search Direction (6th Element)
The sixth element of the Xlookup formula in Microsoft Excel allows you to insert the direction of the search. In fact, you can tell Microsoft Excel in which direction to analyse your document.
You have four different options available, but we must highlight that this is optional and most of the time won’t make any difference. So we want to suggest you put more focus on the other parameters.
Microsoft Excel: Xlookup example
Now that we know how to build a proper Xlookup formula on Microsoft Excel, let’s try to use it properly.
We are dealing with a big database that contains a lot of famous band names. The reason we’re doing this is that we produce merchandise for those bands and we want to find out what sizes of t-shirts are still available. So, everytime we look for a band we’d like to see the available sizes appear on the screen.
Here’s the Excel spreadsheet we will use to build the formula. We’ve put Guns n’ Roses as an example of a search term, ideally when the formula works we’ll be able to replace that one with any other band name.
We want the results to appear in the box in A5, the one under the green cell.
The first element to add to the formula is the search term, in our case the name of the band we want to check out to see what kind of merchandise we have available. In this specific situation, the first value to add to the formula is the cell A2.
The second parameter to add to the formula is the list of values in which we want to find the search term. In this scenario, B2:B15.
The last crucial parameter to add to the formula is the list of matching results that we want to appear in our search results box, C2:C15.
Let’s now put the cursor into the Search Results box in A5 and build the Xlookup formula.
=XLOOKUP(A2, B2:B15, C2:C15)
As we can see, the available sizes for Guns n’ Roses t-shirts are M and L. Then, if we replace the name of the band with another one, we’ll see the search results change automatically.
You’ve just learned how to use the Xlookup in Excel. The Xlookup formula is very easy to learn and gives you the opportunity to spot single elements into big lists of data and to create a new column that displays particular results.
Make sure to practice a lot before using this formula on official documents as it can be difficult at first and you want it to work correctly!
The Xlookup is a great feature included into Microsoft Excel. If you don’t own Microsoft Excel, you must know that it can be found in Microsoft Office Collection.Want to find out more? We got you covered! Read our guide about How Much Does Microsoft Office 365 Cost? Deals, Price and Free Trial.