XLOOKUP Function: 7 Powerful Ways to Simplify Your Excel Lookup

Index

XLOOKUP Function

In Excel, finding the right data quickly is key to working efficiently. If you’re still using older functions like VLOOKUP, HLOOKUP, or LOOKUP, you might be missing out on a much easier and faster way to get things done. Enter XLOOKUP Function a powerful function that will change the way you search for and work with data.

Whether you’re a student, a business analyst, or just someone who loves Excel, XLOOKUP will help you save time and make your work smoother. In this guide, we’ll explain everything you need to know about XLOOKUP in simple terms, from how it works to real-life examples that show how useful it can be. Let’s get started!

What is XLOOKUP Function?

The XLOOKUP function is an advanced Excel lookup tool designed to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP. It allows users to search for a value in a specific range (either vertical or horizontal) and return a corresponding value from another range, with far more options and greater flexibility.

If you do VLOOKUP Vs XLOOKUP power lies in XLOOKUP’S ability to perform both approximate and exact matches, support wildcard and regex searches, and allow users to look up data in both directions. Whether you’re working with large datasets or need to search for values that don’t appear in a consistent format, XLOOKUP is the tool for you.

XLOOKUP Syntax: Understanding the Function’s Structure

To use XLOOKUP effectively, it’s important to understand its syntax. Here’s a breakdown:

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

Arguments Explained:

  • lookup_value: The value you want to search for.
  • lookup_array: The array or range in which you want to search.
  • return_array: The array or range that contains the values to return.
  • if_not_found (optional): A custom value to display if no match is found.
  • match_mode (optional): Specifies how Excel lookup should match the lookup value (e.g., exact match, next larger value, wildcard match).
  • search_mode (optional): Defines whether to search from the first or last entry in the array or perform a binary search.

Key Features of XLOOKUP with Detailed Examples

1. Basic Exact Match:

By default, XLOOKUP performs an exact match. This is the most common use case, where you need to find a value in a list.

Example: Let’s say you have a list of movie titles in column B and their corresponding sales data in column E. You want to look up the sales figure for a specific movie.

Formula:

=XLOOKUP(H4, B5:B9, E5:E9)

In this example, the movie title in cell H4 is searched in the B5:B9 range, and the corresponding sales value is returned from the E5:E9 range.

2. Approximate Match:

XLOOKUP also supports approximate matching, which is perfect for situations like calculating discounts based on quantity.

Example: To calculate a discount based on quantity, where the price is based on the nearest lower quantity, use this formula:

=XLOOKUP(E5, B5:B9, C5:C9, , -1)

In this case, setting match_mode to -1 allows XLOOKUP to find the exact match or the next smallest value in the array.

3. Returning Multiple Values:

One of the standout features of XLOOKUP is the ability to return multiple values with a single formula.

Example: To return several details like First Name, Last Name, and Department based on an employee ID:

Formula:

=XLOOKUP(B5, B8:B15, C8:E15)

Here, XLOOKUP returns an array of values from columns C to E (First Name, Last Name, and Department) for the employee ID in cell B5.

4. Two-Way Lookup:

XLOOKUP can even handle complex two-way lookups by nesting one function inside another.

Example: You need to find a sales value for a specific movie and group.

Formula:

=XLOOKUP(I6, C4:F4, XLOOKUP(I5, B5:B9, C5:F9))

The inner XLOOKUP finds the entire row of sales for a movie, while the outer XLOOKUP matches the group and returns the corresponding sales value.

5. Custom “Not Found” Message:

XLOOKUP lets you customize the message shown when a match isn’t found, instead of returning the #N/A error.

Example:

=XLOOKUP(H4, B5:B9, E5:E9, “Movie not found”)

This formula will return “Movie not found” if the movie title in H4 doesn’t match any entry in the list.

6. Wildcard Match: XLOOKUP can be used to search for partial matches with wildcards, making it useful for finding text that contains certain keywords or patterns.

Excel Tips:
To find book titles that contain a specific word, use the formula below: =TRANSPOSE(XLOOKUP(“*” & G4 & “*”, data[Title], data, , 2))

Here, the * wildcard is used to search for any title that includes the text entered in cell G4.

7. Regex Match: For Excel 365 users, XLOOKUP can also use regular expressions (regex), which allows for more advanced pattern matching.

Example:
To find a product code with a specific pattern, such as 3 uppercase letters, followed by a number, and ending with 2-3 letters, use this formula: =XLOOKUP(“[A-Z]{3}” & F4 & “[A-Z]{2,3}”, B5:B16, C5:C16, , 3)

This regex pattern ensures that XLOOKUP finds product codes that match the described format.

To perform this You can create a custom VBA function to handle the regex pattern and then use it in your Excel worksheet. Here’s how:

  1. Enable Regex in VBA:
    • Open the VBA editor (press Alt + F11).
    • Go to Tools > References.
    • Check the box for Microsoft VBScript Regular Expressions 5.5.
  2. Insert the VBA Code:
    Paste the following code into a new module
    Function RegExMatch(InputString As String, Pattern As String)
    As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
    .Pattern = Pattern
    .Global = False
    .IgnoreCase = False
    If .Test(InputString) Then
    RegExMatch = .Execute(InputString)(0)
    Else
    RegExMatch = "No Match"
    End If
    End With
    End Function
  3. Use the Function in Excel:
    After adding the VBA code, you can use the custom function RegExMatch in your Excel sheet like this:text=RegExMatch(B5, "[A-Z]{3}" & F4 & "[A-Z]{2,3}")

Explanation:

  • The RegExMatch function takes two arguments:
    • InputString: The text to search in (e.g., cell B5).
    • Pattern: The regex pattern (e.g., [A-Z]{3} concatenated with F4 and [A-Z]{2,3}).
  • The function returns the first match or “No Match” if no match is found.

This approach resolves the limitation of XLOOKUP by leveraging VBA for regex functionality. If you prefer other methods like Power Query or Office Scripts, those could also be viable alternatives based on your specific needs.

Other Lookup Functions Or VLOOKUP vs XLOOKUP: Why It’s a Game-Changer

While VLOOKUP, HLOOKUP, and other older lookup functions still have their place, XLOOKUP offers several key advantages:

  • Bidirectional Lookup: Unlike VLOOKUP, which only searches rightward, XLOOKUP can search both left and right of the lookup value.
  • Exact Match by Default: XLOOKUP defaults to an exact match, eliminating the need for additional arguments.
  • Flexible Data Handling: It can work with both vertical and horizontal data, unlike VLOOKUP or HLOOKUP, which are limited to one direction.
  • Returns Full Rows/Columns: XLOOKUP can return an entire row or column, not just a single value, making it much more versatile.
  • Reverse Search Option: XLOOKUP can search from the last entry backward, a feature that is difficult to achieve with VLOOKUP.

For More information you can visit

  1. INDEX-MATCH vs. XLOOKUP: Which One Should You Use? (AbleBits)
  2. 10 Excel Formulas Every Analyst Must Know (DataCamp)
  3. How to Use Wildcards in Excel for Smarter Data Searches (Microsoft Support)
  4. Excel Data Validation: Best Practices & Pro Tips (DataCamp)
  5. VLOOKUP And HLOOKUP: The Ultimate Excel Guide for Effortless Data Search (2025) (Grad Me Up!)

FAQ’s Related to XLOOKUP Function in Excel Formulas

What is the XLOOKUP function used for?

XLOOKUP is a versatile Excel function designed to replace older lookup functions like VLOOKUP and HLOOKUP. It allows you to search for a value in a range and return a corresponding value from another range, supporting both vertical and horizontal lookups with more flexibility

How is XLOOKUP different from VLOOKUP?

Unlike VLOOKUP, XLOOKUP can search in both directions (left or right), defaults to an exact match, and allows custom error messages. It also supports advanced features like wildcard searches, approximate matches, and reverse searches

What are the key arguments of the XLOOKUP function?

The basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The required arguments are lookup_valuelookup_array, and return_array, while optional arguments include custom error messages (if_not_found), match types (match_mode), and search modes (search_mode)

Can XLOOKUP handle multiple criteria or return multiple values?

Yes, XLOOKUP can handle multiple criteria by combining it with other functions like TEXTJOIN or FILTER. It can also return multiple values (e.g., an entire row or column) when used with arrays

Conclusion

XLOOKUP is one of the modern Excel formulas that greatly enhances data lookups by providing flexibility, speed, and ease of use. Whether you’re performing exact or approximate matches, working with multiple values, or using complex pattern matching like wildcards or regex, XLOOKUP is the go-to solution for any lookup task.

Excel tips: By replacing older lookup functions, XLOOKUP simplifies your workflow, especially when dealing with large datasets or when precise control over your search criteria is needed. Whether you’re a beginner or a seasoned Excel pro, adding XLOOKUP to your toolkit will take your Excel skills to the next level.

Ready to unlock the full potential of Excel Formulas? Start using XLOOKUP today and experience its transformative power firsthand!

Facebook
Twitter
Email
Print

Leave a Reply

Your email address will not be published. Required fields are marked *

Newsletter

Sign up our newsletter to get update information, news and free insight.