XLOOKUP in Google Sheets — Two Equivalents and Alternatives

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

• VLOOKUP/HLOOKUP: These functions have similar syntax. But with VLOOKUP you 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 =INDEX(C2:C11,MATCH(E3, A2:A11,0)).

These are more specific than SUMIFS or 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

The =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:

=filter(return_array, lookup_array=lookup_value)

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

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

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

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