XLOOKUP is a useful new function in Microsoft Excel that lets you look up data according to a match pattern. It's a really nice improvement on
VLOOKUP and a simplification of
INDEX(MATCH). And while it may come to Google Sheets in the future, there's no direct equivalent now. Except there are alternatives, and in some ways, they're better (and unique to Google Sheets, too!)
The most common use case for XLOOKUP is to look up a value in a table.
XLOOKUP is an improvement on two existing equations
HLOOKUP: Similar syntax. But with
VLOOKUPyou use a column number in the lookup table, which means when you change the table (which we do all the time!) the formulas break. (also I admit, nobody uses
INDEX(MATCH): This is the more elegant solution to looking up data in a table, because you don't use a column number (which can break). However, it is a little more difficult to read.
These are more specific than
SUMPRODUCT because we're talking about text strings, not values.
There are two main ways you can replace
XLOOKUP in Google Sheets:
FILTER: Easy, fast, awesome
QUERY: Sophisticated, but hard to read
How XLOOKUP works, and what we'll try to replace in Google Sheets
The baseline formula we'll try to replace is:
=XLOOKUP(lookup_value, lookup_array, return_array)
So in the formula above
lookup_value= the country name
lookup_array= the list of country names
return_array= the code(s) to return
Here's a handy Google Sheet with examples of the equivalents of
XLOOKUP, fully implemented.
Method one of doing an XLOOKUP in Google Sheets: FILTER
=filter() formula is one of those things that makes Google Sheets special: it makes it really easy to think in arrays.
Filter is also the best equivalent to XLOOKUP in Google Sheets. It's easy to read, succinct, and quick.
Filter actually returns an array of values. But if you will only ever have a 1:1 match, then you'll only get one value.
The format for filter is easy:
The way to think about it is also easy.
"Give me stuff from the return array where this condition (or these conditions) are met."
In fact, you can do much more sophisticated stuff. With
filter you can
- Make conditions based on multiple variables
- Make more sophisticated conditions (like if the text includes something or starts with something)
- Return a list, and then sort it or do whatever you want with it
In short, using
filter makes you start thinking about Google Sheets very differently.
Method two of doing an XLOOKUP in Google Sheets: QUERY
=query() function is one of the things that makes Google Sheets so damn special. It means you can write SQL queries on a Google Sheet, using it as a makeshift database!
They're not fully fledged queries, but it does include a pretty important subset of the SQL language (the API visualisation language).
In this case, you have to write a
select, which is the 80% of what you need to know in SQL anyway. The format becomes
=query(the_whole_table, "select C where A='"&lookup_value&"'")
It gets a bit hairy adding in the lookup value in query format. That's because you have to use single quotes to surround text strings, then you have to have a break in the query as you reference the cell with the lookup value.
query gets nice is that you can have multiple conditions, using 'and' or 'or' in the query statement. But I admit it gets hard to read.
A few more things you can do is
- Select ranges
- Sort your results
- Return your results as a table, on which to do more operations
The main downside of
query is that, like VLOOKUP, it has hard-coded column labels. Even with this limitation though, it's so powerful that I wanted to leave it in.
If you want to see more about how
query works, Ben Collins has written a pretty good tutorial here.
Old school methods
I don't recommend using
VLOOKUP. It breaks every time you move the columns of the target table.
INDEX(MATCH), the traditional equivalent to XLOOKUP that many a junior consultant, banker, or analyst has to learn, still works perfectly. It's a bit hard to parse the first few times you use it.
If you use INDEX(MATCH) you'll be compatible with everyone else (including Google Sheets), and all the old versions of Excel!