Finally! Every. Single. Time. I have been forced to use VLOOKUPs or INDEX/MATCH I wonder why nobody on the Excel team had still not built a new easier interface. I wish I could only use Pandas but excel is the lingua franca of business.
It’s basically just index match with a sensible default for match’s matching argument.<p>Good stuff. Took too long to make an official implementation of this but glad it’s here
The new function behaves very similar to =SUMIF(), with two additional optional parameters for how to search and match.<p>Easier to understand and solves some issues with =VLOOKUP().
Bill Jelen ("Mr. Excel") has posted an in-depth look at XLOOKUP with more examples:<p><a href="https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlookup-debuts-excel/" rel="nofollow">https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlooku...</a><p>and a video:<p><a href="https://www.youtube.com/watch?v=E5JxX_3Qb7A" rel="nofollow">https://www.youtube.com/watch?v=E5JxX_3Qb7A</a>
So...no patch for older versions of Excel? Or did I miss that part of the announcement?<p>If this isn't available as a patch for older versions of Excel, good luck popularizing the function.
It’s a breakthrough for the consulting world!<p>Joke appart, it’s been long overdue to have such a lookup functions - even more so with sensible parameters as default!
I know the IDE has tooltips, but why don’t they ‘invent’ enums instead of those magic constants for match_mode and search_mode? Does that complicate the parser that much?<p>For VLOOKUP and HLOOKUP, they can’t for backwards compatibility, but this breaks new ground.
Oh man, I would have killed for this when I was in structured products trading at Goldman, the spreadsheets that we were using to keep tabs of positions were beautiful and insane in their complexity and in their raw, insane abuse of VLOOKUP/INDEX/MATCH.<p>I moved from Technology into Trading and all my code access was cut off, and the switch from coding in Slang/SecDb to only being able to use Excel was absolutely brutal. If someone can figure out how to break the Microsoft stranglehold on "business" then there will be a boom in productivity unseen since... Microsoft's suite of business software.
I wonder when we can start using this function without running the risk that our bosses/clients Excel doesn't support this function. Lots of people still use Office 2007 and 2010, so It'll be a while I guess.
I almost cried when I saw the word return_array. Then I realized my mistake. Return_array is the name of an input parameter.<p>How I long for the day I can return multiple values from an excel lookup function. Few users know of such a thing. But it is amazing. So far I have gotten it to work using the most complex formula string I ever made.<p>Link: <a href="https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#vertically" rel="nofollow">https://www.get-digital-help.com/2009/10/25/how-to-return-mu...</a>
I saw a preview of XLOOKUP at the MVP Summit in March and was excited about it (but couldn't say anything about it until it was released).<p>Here's my brief video on XLOOKUP along with a Peace Summit where a staunch INDEX/MATCH user and I formally buried the hatchet n Perth, Australia.<p><a href="https://youtu.be/0KEhR66btUs" rel="nofollow">https://youtu.be/0KEhR66btUs</a>
TL;DR:<p><i>vlookup</i>: (value to lookup, column to search, numbered column from the left, yes/no use range)<p><i>xlookup</i>: (value to lookup, array to search, value to return array)<p>You can also replace <i>hlookup</i> with <i>xlookup</i>. Two optional arguments are 4) exact match or not and 5) go up or down or use binary search. I guess binary search would require a sorted array and improve speed?<p><i>xlookup</i> seems obviously better in retrospect. I wonder if they had any reason not to implement it earlier.
No joke: I spent the better part of an hour a few weeks ago trying to remember how to use VLOOKUP properly to join two lists in Excel before a coworker chatted me that he had done it in 10 seconds by running the Linux `join` command.