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 GSheets, too!)

The most common use case for XLOOKUP is to look up a value in a table.

XLOOKUP in Excel. Not available yet in Google Sheets
XLOOKUP lets you look up a value from a table by matching another value.

XLOOKUP is an improvement on two existing equations

  • VLOOKUP/HLOOKUP: 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. (also I admit, nobody uses HLOOKUP!)
  • 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 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.

Google Sheets - XLOOKUP equivalents demonstrated
XLOOKUP equivalents in Google Sheets

Method one of doing an 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 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

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.

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.

But INDEX(MATCH) still works perfectly. It's a bit hard to parse the first few times you use it. But you'll be compatible with everyone else, and all the old versions of Excel!~