Learn how to master Excel’s XLOOKUP and 2-way XLOOKUP functions. Perfect for modern data analysis, this comprehensive guide includes practical examples and expert tips. Updated for Excel 2024.
by Mihir Kamdar / Last Updated:
After reading this guide, you’ll be equipped with the knowledge to:
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
Are you missing VLOOKUP already? Don’t worry! XLOOKUP is here to make your Excel life easier. Let’s learn how this powerful function can transform your data lookup experience.
The XLOOKUP function is a powerful and flexible tool in Excel that allows you to search for a value in a range or array and return a corresponding value from the same row. It’s a modern replacement for the VLOOKUP function, offering several advantages such as faster performance and a more flexible syntax. One of the standout features of the XLOOKUP function is its support for wildcards and binary search, making it incredibly versatile. Whether you need to perform exact matches, approximate matches, or wildcard matches, XLOOKUP has got you covered.
The XLOOKUP function has the following syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Here’s a breakdown of the arguments:
Optional arguments include:
The Power of XLOOKUP
Think of XLOOKUP as the Swiss Army knife of lookup functions. Unlike its predecessors (VLOOKUP and HLOOKUP), XLOOKUP can:
Simple Yet Powerful Syntax
Basic XLOOKUP Formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Let’s break it down:
XLOOKUP offers more advanced capabilities compared to traditional match functions like VLOOKUP and INDEX-MATCH.
Wildcard matches allow for flexible data searching. Additionally, partial matches can help identify values that may be misspelled or entered incorrectly, enhancing the search process within large datasets.
=XLOOKUP(F2, C:C, A:A, “Not Found”)
This means:
The ‘if_not_found’ argument allows you to specify what to display if the search value is not found. You can also adjust the search criteria to tailor your queries, making it easier to efficiently search through data sets. This flexibility allows users to customize their search parameters when using functions like XLOOKUP.
Imagine finding a value using both row and column criteria – like looking up a price that matches both a product AND a size.
The Formula:
=XLOOKUP(J2, A2:A11, XLOOKUP(J3, B1:G1, B2:G11))
Real Example: Finding Gabriel’s May sales:
Breaking It Down:
XLOOKUP is a versatile lookup function in Excel that can search for a value in a range and return a corresponding value from another range.
XLOOKUP is more flexible – it can look left or right, doesn’t require sorting, and can return multiple values. It’s generally considered an improvement over VLOOKUP.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
By default, it returns #N/A. You can specify a custom value using the [if_not_found] argument.
Yes, by setting the match_mode argument to -1 or 1.
XLOOKUP isn’t just another Excel function – it’s your new best friend for finding data quickly and accurately. Start using it today, and you’ll wonder how you ever lived without it!
