This is the first of a series of posts to introduce commonly used, and productive, Excel formulas. If you are new to Excel these posts will cover the most important formulas to learn, and if you’re experienced hopefully it will contain some tips that you are yet to encounter.
This first post will cover XLOOKUP and I’m somewhat conflicted as my level of excitement in using this fantastic function is offset by incredulousness in how long it has taken Microsoft to provide us with a decent tool (if we ignore Power Query!) for doing one of the most common and laborious tasks – matching separate data sets using a key.
For those of you who have been using Excel long enough to have experienced the inadequacies of VLOOKUP & HLOOKUP, and the complexities of INDEX/MATCH, XLOOKUP will seem like Utopia. It’s structure allows for the quickest execution of the most common relational data linking – “Look up this value, in this column, and return the value from the same row in this other column”. Further complexity and finessing is hidden in optional parameters to be accessed when needed.
In summary the benefits of XLOOKUP over its predecessors are:
• Can look left or right (VLOOKUP shortcoming)• Exact Match is default (no need to specify every single time!)• Can reverse search direction for Last Match (requires sorting)• Can lookup horizontally (replaces HLOOKUP)• Can return spill ranges (entire rows or columns)• Approximate matches on numeric data don't require sorting of data
In addition, as you’ll see in the formula syntax below, XLOOKUP comes furnished with it’s own IFERROR clause ([if_not_found]) for you to plug in a value, or message, should a match not be found.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
If you’re wondering which formula is best for data matching and you have heard about the different options hopefully this post would have saved you some time – forget the others and spend time learning how to use XLOOKUP.
Obviously we haven’t covered enough detail here to teach you how to use the formula so if you would like that please see:
How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 examples)
Discover What XLOOKUP Can Do For YOU (R.I.P. Excel VLOOKUP)
XLOOKUP function in Excel - powerful successor of VLOOKUP
