Monday, March 16th, 2020 / by Angie
LOOKUP functions are functions that return a certain value from a range or array. The VLOOKUP function has been in Excel since the software’s first incarnation in 1995, back in Excel 1 for Macintosh. It was incredibly useful and was for 3rd most used function in Excel after SUM and AVERAGE. Problem was, VLOOKUP only searched vertically, and you’d need to use a different function (HLOOKUP) to look horizontally.
After 35 years, the time for a successor has come, and it’s called XLOOKUP, and it looks up entries both vertically and horizontally at once.
As you can see from Microsoft’s gif above, XLOOKUP needs only three arguments to work: lookup_value, lookup_array, and return_array. Essentially, the value you’re looking for, the array (columns or rows) you want to look for the value in, and what you want the function to return.
There are also advanced variations you can use with XLOOKUP’s optional 4th and 5th arguments. These are if_not_found, match_mode and search_mode.
You can use if_not_found, which lets you specify what to return if a match isn’t found, and match_mode, where you can specify what type of match to perform by passing a number in the function’s arguments.
Zero is the default, and used for exact matches. Both 1 (and -1) allow matches against the nearest smaller (or larger) item when there’s no exact match. 2 is a wildcard for partial matches where the returns ? maches any character and * matches any run of characters.
In search_mode you can configure the type and direction of search. 1 will search first-to-last, -1 will search last-to-first; 2 is a binary search sorted in ascending order, and -2 is the same, but descending.
Yes! Although XLOOKUP is faster than the older functions, if you have sheets where you’re using the old ones and it’s too much of a bother to change it, they’ll still work. But it makes no sense not to use XLOOKUP for newer ones from now on, since it’s simpler to use and has less limitations than its predecessors.
Another addition, and similar to XLOOKUP, XMATCH does pretty much the same, but returns the index of the matching item instead. As with XLOOKUP, XMATCH is much simpler to use and has less limitations than the older MATCH function.
If you’re an Office 365 subscriber in the monthly channel, then you can already use XLOOKUP! For those in the Semi-Annual channel, you’ll have to wait until later in the year, with a specific date yet to be announced.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.