How to use XLOOKUP in Excel?

In this article, learn all about the XLOOKUP Excel function, it’s syntax, parameters and how to use it with real-world xlookup examples.

What is XLOOKUP?

Using XLOOKUP, we can search for an item in a list using the lookup value and return a matching item. For example, you can lookup for salesperson “Jackie” and return their sales amount from the data below using the XLOOKUP function, as depicted above.

It is the newest member of Excel’s lookup function family. You may already know the other members of this group – VLOOKUP, LOOKUP, HLOOKUP, INDEX+MATCH.

How to use XLOOKUP – step by step instructions

Let’s say you have data for salespeople like above and you want to find the Net Sales for “Jackie”. Follow below steps to create the XLOOKUP function in Excel.

1. Write =XLOOKUP( in a cell
2. For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
3. Tip: If you have the name of the person in a cell (like G4), you can point to the cell instead of typing the name
4. Now for the lookup_array, select the names column of your data.
5. and for the return_array, select the net sales column of your data.
6. Close the brackets and hit enter.
7. Congratulations, you’ve just created your first XLOOKUP formula in Excel.

Here is how the formula and result look in my data:

What if the lookup value is not in the lookup array?

One of the most common scenarios of lookups in Excel is not finding the value you are looking for. Imagine, you are looking for the salesperson “Chandoo”, but he doesn’t exist in the dataset. In this case, XLOOKUP will return #N/A error.

But we can use the 4th parameter of XLOOKUP – if_not_found to set an optional value to display when there is an error.

Here is an example formula with that:

=XLOOKUP("Chandoo", B4:B21, D4:D21, "Not found")

How to lookup in the middle with XLOOKUP (INDEX MATCH replacement)?

Let’s say you want to lookup the sales amount of $726 and return the name of the person (in this case, Jessy). Previously, you needed to use the INDEX+MATCH combination for this. But XLOOKUP let’s us specify any range or column for lookup_array portion. so, no more INDEX+MATCH… Yay!

To perform the “net sales” lookup and return the name of the person, we can use the below formula:

=XLOOKUP(G4, G4:G21, G4:G21, "Not found")

The above XLOOKUP example assumes that G4 contains the net sales value you want to lookup.

How to lookup higher than / lower than using XLOOKUP? (approximate match)

Say, you want to find out the closest person with the net sales of $1300. In this case, we don’t have anyone with that value in the data. You can still use XLOOKUP to get the approximate matching value, either next higher or lower in the data.

For this we can use the 5th parameter of the XLOOKUP function – match mode.

There are 4 match modes in XLOOKUP.

• 0 or Exact match. This is the default value for XLOOKUP.
• 1 or next larger match. This looks up for the exact or next highest value in the lookup array.
• -1 or next smaller match. This looks up for the exact or next lowest value in the lookup array.
• 2 or wildcard match. This looks up based on a pattern you have mentioned in the lookup value. More on this further down in the article.

To get the next highest matching value:

=XLOOKUP(G4,D4:D21,B4:B21,,1)
Note: 1 refers to next higher value for match mode.

The above formula returns “Jonathan” in the sample data, as he has the next highest amount – $1316.

To get the next lowest matching value:

=XLOOKUP(G4,D4:D21,B4:B21,,-1)
Note: 11 refers to next lower value for match mode.

This formula returns “John” as he has the next lower value – $1088.

Getting Partial Matches with XLOOKUP (Wildcard / Pattern Matching)

XLOOKUP also allows for a powerful and elegant pattern matching in your data. Let’s say you want to find the net sales for the person whose name begins with the letters Jam. In this case, we can use the match mode 2 (wild card) along with the wildcard operators * (asterisk) and ? (question mark) to create our XLOOKUP.

To find the net sales of the person whose name begins with Jam,

1. Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
2. Here H4 contains the first few letters of the name, i.e. Jam
3. The lookup value is H4 & “*”. This tells XLOOKUP that we want the name to begin with the value of H4 (Jam) and then there can be any number of characters.
4. Rest of the XLOOKUP parameters are as per usual.
5. Don’t forget the match mode operator as 2. We need this for Wild card – pattern matching.

Refer to above illustration for more on the pattern matching xlookup.

Additional Pattern Matching Tricks with XLOOKUP

Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.

Situation	Formula	Explanation	Sample Result
Name ends with ved	=XLOOKUP(“*ved”, B4:B21,D4:D21,”No such person”,2)	To find a word ending with, we use * (asterisk) at the beginning of the pattern	Javed, $2277
Name contains ack	=XLOOKUP(“*ack*”, B4:B21,D4:D21,”No such person”,2)	We can use two * symbols – one at the beginning of the pattern and one at the end.	Jackie, $1610
Name has 5 letters and begins with Je	=XLOOKUP(“Je???”,B4:B21,D4:D21,,2)	In this case, we can use the ? (question mark) symbol to indicate that we can have any one character. So the pattern is Je???	Jessy, $726
Name begins with J and ends with d	=XLOOKUP(“J*d”, B4:B21, D4:D21,”No such person”, 2)	J*d means the lookup value begins with J, contains any number of letters and ends with d	Javed, $2277

What if there are two matching values?

If you have more than one matching item for the lookup value in your data, XLOOKUP, just like all other lookup functions in Excel, will always return the first matching item’s corresponding value.

As you can see in the above illustration, we have two sales persons with the name Johnson in our data.

When using =XLOOKUP(“Johnson”, B4:B21, D4:D21) we are going to get the net sales of the first Johnson ie $1540.

But what if I need to get the second or third or all the matching values?

In this case, you can use the new FILTER() function in Excel to get the second or all matching items.

Here is the formula.

=FILTER(D4:D21,B4:B21="Johnson")

For more information on FILTER function, refer to this article.

Horizontal Lookup with XLOOKUP (HLOOKUP replacement):

XLOOKUP works just as good with horizontal data too, thus replacing any need for HLOOKUP function.

In the above example, I have monthly budget table and I want to lookup the budget value for April 2024.

We can use this XLOOKUP formula to do just that.

=XLOOKUP(C10,C3:N3,C4:N4)

Returning entire row of information with XLOOKUP:

Another powerful feature of XLOOKUP is that it can return multiple values all corresponding to the same lookup value. For example, I want to see the budget, actual and balance information for the month of April 2024, from my budget spreadsheet below. We can use XLOOKUP for that easily.

=XLOOKUP(C10,C3:N3,C4:N6)

In the above formula, by using multiple rows (C4:N6) as the return array, we can return all corresponding values for the lookup value in C10 – ie April 2024. Excel will automatically spill these values into separate cells on the worksheet.

XLOOKUP – things to keep in mind:

When using XLOOKUP, you must keep these points in mind:

• XLOOKUP needs Excel 365 or Excel on the web or Excel 2021: This function is not available in all the versions of Excel. So check your version of Excel before you start using xlookup. If you notice #NAME errors when working with XLOOKUP, that means your version of Excel does not support this function. Instead, use INDEX+MATCH formula.
• Use the If not found option to fix errors: To avoid any lookup errors (#N/As), use the fourth argument of the XLOOKUP function – if_not_found.
• Lock cell references when creating lookups: If you are writing multiple XLOOKUPs in a range, don’t forget to lock your cell references to absolute mode (change B4:B21 to $B$4:$B$21) so that when you drag or fill the formula down, your lookup and return array ranges don’t change. This is a common mistake and you will end up with wrong results. (absolute vs. relative references)
• Or better yet, convert your lookup data to a table: A simple fix to the relative reference issue is to use tables on your data. This way, you can write simple XLOOKUP formulas like this: =XLOOKUP(“Jackie”,sales[Sales Person],sales[Net Sales])
  • Learn more about how to use Excel tables with your data.
• In case of multiple matches, XLOOKUP always returns the first (or last matching item, if you used search mode option) value. This is why it is important to also learn how to use the FILTER function in Excel.

How is XLOOKUP better?

• XLOOKUP makes the most used formula in Excel straight forward and less error prone. You just write =XLOOKUP(what you want to find, the list, the result list) and boom, you get the answer (or #N/A error if the value is not found)
• Looks up exact match by default: One of the annoyances of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes that by doing exact matches by default. You can use match mode parameter to change the lookup behavior if you want.
• 4th parameter to support value not found scenario In most business situations, we are forced to wrap our lookup formulas with IFERROR or IFNA formulas to suppress errors. XLOOKUP offers 4th parameter (read more about it below) so you can tell what default output you want if your value is not found.
• XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wildcard searches and faster options to search sorted lists.
• It returns reference as output, not the value. While this may not mean much for normal users, pro Excel user’s eyes light up when they discover a formula that can return refs. That means, you can combine XLOOKUP outputs in innovative ways with other formulas. For example: XLOOKUP to create dependant drop down in Excel.
• It is so much cooler to type, you just type =XL. I am not sure if this is a happy coincidence but saying =XL to get this formula is just awesome.

XLOOKUP Syntax

Simple case:

=XLOOKUP(what you want to look, lookup list, result list)

=XLOOKUP("Jackie", sales[Sales Person], sales[Net Sales]) 

returns Jackie’s [Net Sales] if the name can be found in [Sales Person]

Optional parameters:

By default, you just need 3 parameters for XLOOKUP, as shown above. But you can also use 4th, 5th and 6th parameter to specify how you want the lookup to be done.

4th parameter for XLOOKUP: IF not found (no more IFERROR!!!)

The newly introduced XLOOKUP has an even newer feature. It now supports if not found option. This is the 4th parameter.

For example, use:

=XLOOKUP(“Chandoo”, sales[Sales Person], sales[Net Sales],”Value not found”) to return “Value not found” if the lookup value is not available in the search column – sales[Sales Person].

5th parameter for XLOOKUP: Match mode or type:

Use this to tell Excel how you want your MATCH to happen. The default is 0 (exact match) but you can also use these other options, shown below.

6th parameter for XLOOKUP: Search mode

Try this if you want to search from bottom to top. The default direction is top down (1).

XLOOKUP with two sheets

If you have data in two seperate sheets (or files), you can use XLOOKUP to quickly combine the data and get what you want.

Refer to my XLOOKUP with two sheets article for detailed instructions and formula examples.

XLOOKUP – Video Tutorial:

Here is a simple but effective video tutorial on how to use XLOOKUP function and how it can replace VLOOKUP and INDEX+MATCH functions. You can also watch the xlookup tutorial on my channel.

Download XLOOKUP examples – workbook

Click here to download the 13 XLOOKUP examples file.

And one more example file, this with INDEX+MATCH replacements.

Final thoughts on XLOOKUP

In my opinion, XLOOKUP is a terrific function and a must have for any data professional. It is part of my essential Excel formulas list for data analysts.

When we nest XLOOKUP formulas, we can also perform more complex lookups like 2-WAY lookups or search across different worksheets. I discuss some of these advanced scenarios in a recent video on my YouTube channel. Please watch it here.

The only downside of XLOOKUP is the compatibility. It doesn’t work in all versions of Excel. For this reason, I still think there is value in learning how to use VLOOKUP and INDEX MATCH functions.

Do let me know what you think about XLOOKUP and if you have any questions about it using the comments section.

Learn more about Data Analysis with Excel:

This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.

• How to use Excel Tables – 13 time saving tips
• How to use Dynamic Array functions in Excel such as Filter, Unique, Sort etc.
• Working with Power Query in Excel to save time and shine at work
• Know more about XLOOKUP with my course