10 ms·
XLOOKUP for Excel
- Someone 7y agoI 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? For VLOOKUP and HLOOKUP, they can’t for backwards compatibility, but this breaks new ground.
- make3 7y agothey likely either made utility studies for this, or all of Excel already works that way and they don't want to break the expectations of the users
- steve19 7y agoFinally! 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.
- z3c0 7y agoIt's almost impressive how long it's taken for Excel to comfortably mimic "SELECT Prefix FROM Whatever WHERE Country = 'Brazil'" I have no doubts why it's the de facto data tool, but this is laughably overdue.
- fzumstein 7y agoYou can use Pandas via the xlwings package (https://www.xlwings.org https://www.xlwings.org)
- cm2187 7y agoBecause nobody in the Excel team uses Excel and it shows.
- intern4tional 7y agoNo, they just don't use it for your specific scenario. Disclaimer: Work at MS. We use Excel plenty, we just tend to do things differently than many other businesses as things that shouldn't be done in Excel we don't do in Excel (since we have other tooling available). The challenge is that in reality, people are diverse and will use Excel as long as they can as they don't want to buy other software, lack skills to implement it differently, or for some other reason need to keep data in Excel. Software like Excel has been around for so long and has so many edge cases in how it is used that the dev teams could spend 100% of their time adding features and still probably not touch some potentially large user base ones.
- cm2187 7y agoI'd like to believe you but some of the beefs I have are not edge case at all. Top of my head, a few examples (in addition of the index/match): 1. the fx "Insert function" window, with a search feature that just doesn't work, a listbox that shows only 7 results and is not resizable (when the built-in functions alone is a huge list, plus all custom udf). Same thing for the Function Arguments window. 2. excel incapable of preserving the default for calculation mode (automatic/manual) between sessions 3. excel recalculating the whole sessions when opening a csv file even when calcs are on manual 4. terrible default formats (serious people don't want thick lines, 3d charts and all that crap) 5. Recent regression: can't copy a table from excel to powerpoint while preserving the format, formatting a table in powerpoint is a pain in the butt, default format are just bad 6. No way to link a powerpoint to a spreadsheet. Think all the bankers, consultants, engineers, who must present the result of some analysis, the data is in excel, the deliverable is a powerpoint deck, how do you link the two. Only ways I know are awefully manual and/or unstable and/or result in tables displayed as images in powerpoint 7. VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) I could go into more edge case things but these are mass usage features.
- dragonwriter 7y ago> VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) VSTA was not canned [0], but core Office apps never supported it. IIRC, the only Office app that ever used (uses?) VSTA instead of VBA is InfoPath. VSTA was more of a replacement for VBA as an offering for other people (VBA, as well as being used in office, was available for others to use in their apps), who didn't have Office’s backward compatibility constraints. [0] VSTA 2019 SDK is available here: https://www.microsoft.com/en-us/download/details.aspx?id=58316&WT.mc_id=DX_MVP4025064 https://www.microsoft.com/en-us/download/details.aspx?id=583...
- sin7 7y agopandas is inconsistent and verbose. Isn't SQL a better alternative? Or even dplyr? Heck, I would learn one of those array languages such as J or APL rather than use pandas.
- skrebbel 7y ago> pandas is inconsistent and verbose. Isn't SQL a better alternative? Wait, there's a thing that's more inconsistent and verbose than SQL?
- ramraj07 7y agoHow is SQL inconsistent?
- jasode 7y agoThe syntax and grammar of SQL commands do not have an elegant symmetry. Examples: The ordering is not always name of table and then names of columns... Write data: UPDATE customer SET membership_plan='PLATINUM' Read data (incorrect syntax if specifying table name then column name): SELECT customer GET membership_plan -- incorrect Read data (correct syntax of columns then table): SELECT membership_plan FROM customer For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE customer SET membership_plan='PLATINUM', timezone='UTC' INSERT INTO customer (membership_plan,timezone) values ('PLATINUM','UTC') Prepositions... Prepositions before table name: SELECT FROM t, INSERT INTO t, DELETE FROM t No preposition before table name: UPDATE t For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Yes, one eventually gets used to the cosmetic inconsistencies but it's annoying for beginners because memorizing 1 of the 4 commands doesn't really reward you with the ability to predict the grammar of the other 3 commands. You have to learn the different quirks of the 4 commands. Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! [To downvoters, I'm willing to be persuaded that I'm wrong and that SQL syntax is actually "consistent" so please reply with your counterargument.]
- deleted 7y ago[deleted]
- noahmbarr 7y agoKilling most reasons for the index() + match() combo!
- gcatalfamo 7y agoIt depends which is faster. The video shown is not really the real world use case.
- thatthatis 7y agoBut not the 2 dimensional index(match, match) use case, unfortunately
- kyberias 7y agoHave you asked Joel Spolsky about this?
- b_tterc_p 7y agoIt’s basically just index match with a sensible default for match’s matching argument. Good stuff. Took too long to make an official implementation of this but glad it’s here
- QuantumGood 7y agoAgree. Looks like I can finally get more people using the functionality provided by INDEX([returninfo],MATCH()). Previously, folks would get too confused initially to commit it to memory. MrExcel has this exact comparison: Youtube.com/watch?v=8dwmvTka3gs
- smitty1e 7y agoI, for one, await YLOOKUP, so that we can go vertically, horizontally, and tab-wise through our workbooks, for that true 3D feel.
- TheCraiggers 7y agoShouldn't that be ZLOOKUP then?
- ekingr 7y agoIt’s a breakthrough for the consulting world! Joke appart, it’s been long overdue to have such a lookup functions - even more so with sensible parameters as default!
- Deutscher 7y agoWill this show up in Office 2019?
- prokes 7y agoThe new function behaves very similar to =SUMIF(), with two additional optional parameters for how to search and match. Easier to understand and solves some issues with =VLOOKUP().
- credit_guy 7y agoSo, instead of using SUMIF, I always use SUM or SUMPRODUCT using the trick that in Excel True and False are 1 and 0 when used in arithmetical operations. This is unbelievably flexible. For example, if you want to sum all the elements of column A if the element in B is between 1 and 10 then you have SUM(A1:A100 * AND(B1:B100 >=1, B1:B100 <=10)). You need to press Ctrl+Shift+Enter, as this is an array formula, but otherwise it works like a charm.
- toasterlovin 7y agoA small but important difference between this new function and SUMIF: SUMIF will sum all values in the array that match your search criteria, but this function will only return a single value.
- ttul 7y agoAnyone here on the GSuite team who can get this into Sheets stat?
- fomopop 7y agoNot identical, but this fuzzy match is a life changer. https://gsuite.google.com/marketplace/app/flookup/593806014962 https://gsuite.google.com/marketplace/app/flookup/5938060149...
- rayshan 7y ago+1 works well but long calculate times
- medmunds 7y agoIf you're using Google Sheets, the QUERY function [1] can do all kinds of lookups. For complex logic, I find its SQLish syntax a lot more readable than mixtures of VLOOKUP and IF and other functions (but that might just be me). So, um, anyone here on the Office team who can get QUERY into Excel? [1]: https://support.google.com/docs/answer/3093343?hl=en https://support.google.com/docs/answer/3093343?hl=en
- nefitty 7y agoMy office is split between Excel and Sheets users. I was once trying to mash-up my Sheets knowledge with an Excel colleague. I told him it might be easier to accomplish his task with Sheets because query() uses a SQL-like syntax. I told him Excel doesn't have that. He responded, "lmao you can do that with vb". I looked up instructions on how to that and realized he had no idea what he was missing.
- coagmano 7y agoAnything that requires VB is not friendly. Of course there's a sheets equivalent using javascript, which you can say the same thing about
- eyeball 7y agoNow give me count distinct in pivot tables, and limit rows in a book only by the ram in my machine.
- kvna 7y agoIf you add your table to the data model you can do count distinct with a pivot table
- kerng 7y agoThis is one of the features that I was wondering why it wasn't there from the beginning (according to article Excel 1 with VLOOKUP shipped 1985).
- ggcdn 7y agoIt doesn't look like either xmatch or xlookup can lookup based on multiple search criteria? That's my main use case for index(match()).
- anonu 7y agoMy thought abstraction on this: Joins are what make database technologies... If you can call Excel a database technology... So powerful.
- mabbo 7y agoA decade ago as an intern at Microsoft, I attended a talk by <distinguished engineer whose name I forget> about data systems. The thing I remember from it was: "In terms of total data stored, what data system do you think is the biggest in the world today? ... Probably Excel. Probably Excel by a lot." Excel has a simple mental model combined with powerful tooling. It's the most beautiful means to get non-technical people to start to think like a programmer. An acquaintance of mine tells a great story of how Excel and VBA got him into software: https://news.ycombinator.com/item?id=7950736 https://news.ycombinator.com/item?id=7950736.
- TacoToni 7y agoThis is amazing. I also found a love for programming after i learned some spaghetti VBA to create automated sales reporting at my finance job. Finally starting up CS degree in Jan 2020.
- agumonkey 7y agoExcel is an hybrid ball of things already. Reactive dataflow is one of them. So Join is not that much more to add.
- mikorym 7y agoExcel is to me basically a sequel database broken in a few key ways. And some people (a lot of...?) like it that way.
- EForEndeavour 7y agoAs an aside, this is the very first time I've ever seen "SQL" written out as "sequel."
- nashashmi 7y agoI almost cried when I saw the word return_array. Then I realized my mistake. Return_array is the name of an input parameter. 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. Link: https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#vertically https://www.get-digital-help.com/2009/10/25/how-to-return-mu...
- cm2187 7y agoOr do a lookup over multiple columns without having to do some ugly concatanations
- macky360 7y agoTime to start crying. You can return an array. Just provide a 2D array to the return_array parameter.
- nashashmi 7y agoFrom what I understand about xlookup, it will only return back the first value and not multiple values.
- parsimo2010 7y agoExcel has allowed array formulas for literally decades. It’s not as complicated as the link you posted makes it seem. You just need: 1. A formula that supports array returns (like MMULT) 2. Know how big the returned array dimensions are 3. Highlight that array size, type in the formula, and press ctrl+shift+enter Returning multiple matches from a single criteria isn’t what VLOOKUP was designed for, so rather than complain that a hammer does a bad job at screwing in nails, search for a screwdriver. Excel has tons of data filtering and table features, and allows DB connections for SQL queries.
- nashashmi 7y agoNever knew of mmult. Will try to look at that technique to simplify the formula. Aside from that, another user posted excel campus example of filter function. That also returns an array. And pretty much does what I need. However, it is only for office 365 insider and not to be released anytime soon.
- Ives 7y agoI 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.
- miles 7y agoBill Jelen ("Mr. Excel") has posted an in-depth look at XLOOKUP with more examples: https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlookup-debuts-excel/ https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlooku... and a video: https://www.youtube.com/watch?v=E5JxX_3Qb7A https://www.youtube.com/watch?v=E5JxX_3Qb7A
- QuantumGood 7y agoAnd an Index Match comparison: youtube.com/watch?v=8dwmvTka3gs
- mikorym 7y agoTL;DR: vlookup: (value to lookup, column to search, numbered column from the left, yes/no use range) xlookup: (value to lookup, array to search, value to return array) You can also replace hlookup with xlookup. 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? xlookup seems obviously better in retrospect. I wonder if they had any reason not to implement it earlier.
- mrtweetyhack 7y agoThank goodness. VLookup was always a broken mess. Hope Google Sheets and LibreOffice picks this up soon.
- hanniabu 7y agoCurious why it wasn't named HLOOKUP since v=vertical and h=horizontal
- kasperni 7y agoXLOOKUP is a replacement for both VLOOKUP and HLOOKUP
- deleted 7y ago[deleted]
- citrusx 7y agoSo, is this on the roadmap for LibreOffice yet?
- virgulino 7y agohttps://m.xkcd.com/2180/ https://m.xkcd.com/2180/
- AdmiralAsshat 7y agoNo 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.
- OzCrimson 7y agoYour co-worker couldn't tell you about doing a join in Excel's Power Query?
- robomartin 7y agoSo...no patch for older versions of Excel? Or did I miss that part of the announcement? If this isn't available as a patch for older versions of Excel, good luck popularizing the function.
- arthurcolle 7y agoOh 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. 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.
- OzCrimson 7y agoI 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). 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. https://youtu.be/0KEhR66btUs https://youtu.be/0KEhR66btUs