XLOOKUP for Excel

[steve19]

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.

[z3c0]

It'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]

You can use Pandas via the xlwings package (https://www.xlwings.org)

[sin7]

pandas 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.

[cm2187]

Because nobody in the Excel team uses Excel and it shows.

[b_tterc_p]

It’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]

Agree. 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]

I, for one, await YLOOKUP, so that we can go vertically, horizontally, and tab-wise through our workbooks, for that true 3D feel.

[fooqux]

Shouldn't that be ZLOOKUP then?

[prokes]

The 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]

So, 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]

A 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.

[miles]

Bill Jelen ("Mr. Excel") has posted an in-depth look at XLOOKUP with more examples:

https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlooku...

and a video:

https://www.youtube.com/watch?v=E5JxX_3Qb7A

[QuantumGood]

And an Index Match comparison: youtube.com/watch?v=8dwmvTka3gs

[anonu]

My thought abstraction on this: Joins are what make database technologies... If you can call Excel a database technology... So powerful.

[mabbo]

A 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.

[mikorym]

Excel is to me basically a sequel database broken in a few key ways. And some people (a lot of...?) like it that way.

[agumonkey]

Excel is an hybrid ball of things already. Reactive dataflow is one of them. So Join is not that much more to add.

[ttul]

Anyone here on the GSuite team who can get this into Sheets stat?

[medmunds]

If 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

[fomopop]

Not identical, but this fuzzy match is a life changer.

https://gsuite.google.com/marketplace/app/flookup/5938060149...

[robomartin]

So...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.

[ekingr]

It’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!

[Someone]

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?

For VLOOKUP and HLOOKUP, they can’t for backwards compatibility, but this breaks new ground.

[make3]

they 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

[arthurcolle]

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.

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.

[Ives]

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.

[gourabmi]

I wonder if this function can be emulated on older versions of Excel.

[nashashmi]

I 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-mu...

[parsimo2010]

Excel 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.

[longemen3000]

Fot what I know, Microsoft is working on an improvement of arrays in excel: https://www.excelcampus.com/functions/dynamic-array-formulas...

[macky360]

Time to start crying. You can return an array. Just provide a 2D array to the return_array parameter.

[cm2187]

Or do a lookup over multiple columns without having to do some ugly concatanations

[OzCrimson]

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).

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

[kerng]

This 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).

[noahmbarr]

Killing most reasons for the index() + match() combo!

[gcatalfamo]

It depends which is faster. The video shown is not really the real world use case.

[thatthatis]

But not the 2 dimensional index(match, match) use case, unfortunately

[ggcdn]

It doesn't look like either xmatch or xlookup can lookup based on multiple search criteria? That's my main use case for index(match()).

[citrusx]

So, is this on the roadmap for LibreOffice yet?

[eyeball]

Now give me count distinct in pivot tables, and limit rows in a book only by the ram in my machine.

[kvna]

If you add your table to the data model you can do count distinct with a pivot table