
XLOOKUP for Excel - kbumsik
https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
======
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.

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

~~~
skrebbel
> 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
How is SQL inconsistent?

~~~
jasode
The 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.]

~~~
shkkmo
Mysql certainly has some syntactic warts, but you picked what I consider
rather odd complaints.

> The ordering is not always name of table and then names of columns

I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run
without providing a table reference. Also, the select doesn't take a simple
list of column names, but is a more complicated tool for organizing and
formatting data. (This section would not by syntactically or semantically
similar to anything in the INSERT or UPDATE that is not itself part of a
SELECT statement)

> 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 and INSERT both support the "assignment_list" syntax. So they are
perfectly consistent. UPDATE also supports other formats (such as VALUES or
SELECT).

The real inconsistency I would have called our here is that while INSERT can
be used with subqueries (via VALUES or with a SELECT clause), UPDATE must use
joins to achieve a similar functionality.

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

Well "INTO" is an odd case, it is completely optional and serves no syntactic
purpose. The FROM portion of SELECT statements does serve a syntactic purp0se
but isn't actually required if it is not needed (E.G `SELECT "hello world"`).
The inconsistent thing here is that the FROM portion of the DELETE syntax
(which serves no syntactic purpose) is mandatory and not optional.

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

If you look at the spec, SQL is pretty good at reusing syntactic units. You
can see this in the documentation for both MySQL and Postgres.

The worst thing in terms of SQL consistency is the syntactic and semantic
differences between different flavors of SQL. This greatly reduces the value
that SQL could have as a plug and play query format.

~~~
jasode
_> Mysql certainly has some syntactic warts, [...] UPDATE and INSERT both
support the "assignment_list" syntax. So they are perfectly consistent._

(I was making generic comments about _SQL_ so not sure why you interpreted my
post as criticizing _MySQL_.)

Anyways an FYI for any readers not aware... MySQL has _non-standard
extensions_ [0] to the reference SQL language. Other databases like Oracle, MS
SQL Server, and SQLite _do not_ have INSERT with optional "assignment_list"
syntax.

 _> Well "INTO" is an odd case, it is completely optional _

Another example where MySQL is a special case because its proprietary
extensions makes "INTO" optional. In other databases like SQLite, the "INTO"
is absolutely required because omitting it in "INSERT customer" returns a
syntax error.

 _> I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run
without providing a table reference._

Yes, the ability for SELECT to leave out the table name is handy for functions
like "SELECT date();" but its optionality would not have violated consistency
with table-then-column ordering of INSERT & UPDATE. In cases where SELECT
needs to specify table(s), it would have been more consistent grammar by
putting the table names _before_ the columns like INSERT & UPDATE. (This would
have also greatly simplified editors with intellisense since putting the table
first would let the parser know which columns to list as autocomplete
suggestions. E.g. in Navicat SQL text editor, to get a list of autocomplete
column names, I have to first type out "SELECT FROM customer" then move the
cursor _backwards_ to the left of "FROM" and then start typing "mem" to see
"membership_plan" as an autocomplete suggestion.)

[0] [https://dev.mysql.com/doc/refman/8.0/en/extensions-to-
ansi.h...](https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.html)

~~~
shkkmo
Hmm, I obviously failed at researching my post properly. When looking for a
page listing the differences and found this:

[https://dev.mysql.com/doc/refman/8.0/en/differences-from-
ans...](https://dev.mysql.com/doc/refman/8.0/en/differences-from-ansi.html)

I completely missed the extensions page you listed.

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

~~~
TheCraiggers
Shouldn't that be ZLOOKUP then?

~~~
13of40
That's a good name if you're a techie, but you know it'll be "PowerLookup
Live" by the time the marketing people are through with it.

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

------
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...](https://www.mrexcel.com/excel-tips/the-vlookup-slayer-xlookup-
debuts-excel/)

and a video:

[https://www.youtube.com/watch?v=E5JxX_3Qb7A](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.

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

~~~
EForEndeavour
As an aside, this is the very first time I've ever seen "SQL" written out as
"sequel."

~~~
mikorym
Not deliberate; I just write things out as I say them and didn't review.

------
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](https://support.google.com/docs/answer/3093343?hl=en)

~~~
nefitty
My 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
Anything that requires VB is not friendly. Of course there's a sheets
equivalent using javascript, which you can say the same thing about

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

~~~
Ives
I was thinking about that too. Although any form of delivering automatically
executing code is just another attack vector. It's macro's all over again.

------
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...](https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-
values-using-vlookup-in-excel/#vertically)

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

~~~
nashashmi
Never 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.

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

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

------
mikorym
TL;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.

------
Deutscher
Will this show up in Office 2019?

~~~
eyeball
I should get this around 2026 when my company finally updates from office
2016.

------
virgulino
[https://m.xkcd.com/2180/](https://m.xkcd.com/2180/)

------
kyberias
Have you asked Joel Spolsky about this?

------
hanniabu
Curious why it wasn't named HLOOKUP since v=vertical and h=horizontal

~~~
kasperni
XLOOKUP is a replacement for both VLOOKUP and HLOOKUP

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

~~~
OzCrimson
Your co-worker couldn't tell you about doing a join in Excel's Power Query?

