XLOOKUP Function in Excel: A Modern VLOOKUP
If you’re still using the VLOOKUP function in Excel, then you’re missing out on some of the newer and more flexible options that XLOOKUP offers. XLOOKUP is a modern replacement for VLOOKUP, and it has several advantages over older functions. In this blog post, we’ll take a look at how to use XLOOKUP and some of its key features.
What Does The XLOOKUP Function Do?
XLOOKUP is a function that’s used to look up and return data from a specific cell or range of cells. It can be used to find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (see match type and search mode details below).
Where To Find The XLOOKUP Function?
Option 1:
You can find the XLOOKUP function in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
- lookup (required) – The lookup value.
- lookup_array (required) – The array or range to search.
- return_array (required) – The array or range to return.
- not_found (optional) – Value to return if no match found.
- match_mode (optional) – 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
- search_mode (optional) – 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.
Let’s Take a Look at Some Real-World Examples
Excel Workbook
Coming Soon
Video Walkthrough
Coming Soon
Example 1 – Basic XLOOKUP
Coming Soon
Your turn: Use the workbook and XLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green.
Example 2 – XLOOKUP Function using Match_Mode
Coming Soon
Your turn: Use the workbook and XLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green.
Example 3 – XLOOKUP Function using Search_Mode
Coming Soon
Your turn: Use the workbook and XLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green.
Benefits of XLOOKUP
XLOOKUP offers several important advantages, especially compared to VLOOKUP:
- lookup data to the right or left of lookup values
- can return multiple results (example #3 above)
- defaults to an exact match (VLOOKUP defaults to approximate)
- can work with vertical and horizontal data
- can perform a reverse search (last to first)
- can return entire rows or columns, not just one value
- can work with arrays natively to apply complex criteria
Office 365 Only
XLOOKUP is a new function and can only be found in Office 365. If you are using an older version of Excel it may be time to upgrade.
Let’s Recap
The XLOOKUP function is a more flexible and versatile replacement for the VLOOKUP function. It can be used to look up data in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. XLOOKUP also offers several key advantages over older functions, including the ability to search data starting from the first or last value.
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.