XLOOKUP is a useful and relatively new function in Microsoft Excel that lets you look up data according to a match pattern.
XLOOKUP is a really nice improvement over
VLOOKUP, and is 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 to XLOOKUP in Google Sheets, and in some ways, they’re better — and unique to Google Sheets, too!
Context: What is XLOOKUP and why do you use it?
You might like to skip ahead if you know this already. This part is more for Google Sheets users who have been told someone who’s an Excel user “just use XLOOKUP”.
The most common use case for XLOOKUP in Excel is to look up a value in a table.
For example in the below table, to look up
E3 in the range
A2:A11 and return a value from
C2:C11, you use the function
=XLOOKUP(E3, A2:A11, C2:C11). Quite intuitive.
XLOOKUP is an improvement on two existing equations
HLOOKUP: These functions have 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. Fewer people use HLOOKUP regularly.
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 cumbersome to read. For example, the above function would be
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 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
filter 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.
Old school methods to avoid (looking at you, VLOOKUP)
I don’t recommend using
VLOOKUP. It breaks every time you move the columns of the target table.
Every time someone hands me a workbook with VLOOKUP I know that they haven’t gotten very far along in their spreadsheet journey.
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.
The major advantage of
INDEX(MATCH) is compatibility. If you use
INDEX(MATCH) you’ll be compatible with everyone else (including Google Sheets), and all the old versions of Excel!