XLOOKUP in Google Sheets

If you are used to working in Microsoft Excel, you may be familiar with the XLOOKUP function.

When working in Google Sheets you may be wondering the best way to create a similar function, since there is not an equivalent function available at this time.

Well luckily, there are a few good alternatives we can use to achieve similar results.

In this tutorial, I will show you the best ways to create an XLOOKUP alternative in Google Sheets.

Does Google Sheets Have an XLOOKUP Function?

At this time Google Sheets does not have a function that is an exact equivalent of XLOOKUP.

This may be something they will add in the future, but at this time it is not a built-in function.

There are some other ways that we can do something similar, which I will cover later on in this post.

What is XLOOKUP?

XLOOKUP is a kind of lookup function that is used to find things in a table or range by row. 

It is somewhat of a newer function that was released for Excel in 2019. It is kind of similar to other lookup functions like VLOOKUP, HLOOKUP, and INDEX(MATCH), though it is much easier to use and more robust.

XLOOKUP has become the preferred lookup method for many spreadsheet users using Microsoft Excel.

This is the syntax of XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

• lookup_value – this is the value you are searching for
• lookup_array – this is the range or array to search
• return_array – this is the array or range to return
• if_not_found – this is an optional argument. You can specify what is returned is a match is not found. This will return #N/A if left blank
• match_mode  – this is an optional argument. This controls how the function will find matches. Options are 0, -1, 1, 2
• search_mode – this is an optional argument. This controls how the function will search. Options are 1, -1, 2, -2

Here is a basic example of how XLOOKUP works:

In this example, my formula is =XLOOKUP(F2,A2:A5,C2:C5).

What this formula does is it will search for my value in F2, in cell range A2:A5, and return the matching value in cell range C2:C5 which in this case is 20.

Hopefully, you can see why this function is useful. not only is it very easy to use, but it also doesn’t search by column number, it searches by the exact column, which means when you change your table the formula won’t break as it does with other lookup functions.

This function also has other benefits, like the ability to return an array and not just a single value. 

Now that we have covered what this function does and how to use it, let’s take a look at how we can accomplish this is Google Sheets.

Best XLOOKUP Alternatives in Google Sheets

FILTER

FILTER is the best way to achieve something similar to XLOOKUP in Google Sheets.

It is absolutely a must-know function for regular users of Google Sheets.

To use FILTER like XLOOKUP we will be using it with this syntax:

=FILTER(return_array, lookup_array=lookup_value)

• return_array – this is the array or range to return
• lookup_array – this is the range or array to search
• lookup_value – this is the value you are searching for

For more information on this function check out our full guide on using the FILTER function.

Here is an example of how to use FILTER as a look up function in your spreadsheet:

The exact formula that I have used in this example is: =FILTER(C:C,A:A=F2)

What this formula is doing is returning the value from range C:C where the value in range A:A is equal to F2.

This is just one way that you can use FILTER, the function is even more powerful because it can:

• Return an array if there is more than one matching value
• Filter based on conditional statements
• Include conditions on more than one variable

QUERY

Another option that we have is to use the QUERY function. This function allows you to write SQL queries in your spreadsheet.

If you already know a little bit of SQL, you might find this method to be easy, however, if you are not familiar with it, it might take a little bit of practice to get down.

To use QUERY like an XLOOKUP function you will need to use this syntax:

=QUERY(lookup_table, “select return_column_letter where lookup_column_letter='”&lookup_value&”‘”)

• lookup_table- this is the table that contains all of the data you are working with
• return_column_letter – the letter of the column where the value that you want to return is
• lookup_column_letter – the letter for the column that you are searching in to match your lookup_value
• lookup_value – this is the value you are searching for

Here is how this formula would be set up in our sheet using the previous example:

The formula used here is =QUERY(A2:D5,“select C where A='”&F2&“‘”)

So my entire lookup_table is in cell range A2:D5 and I want to return the value from column C where column A matches my lookup_value of F2.

INDEX(MATCH)

Using the INDEX and MATCH functions together is another option. I have previously covered combining the INDEX and MATCH functions together in a previous post, so you can read that if you want a more in-depth guide on how to use these.

For our purposes we will be using them with this syntax:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• return_range – the range that contains the value that you want to return
• lookup_value – the value you are searching for
• lookup_range – the range that contains the lookup_value that you are searching for

The formula in this example is: =INDEX(C:C,MATCH(F2,A:A,0))

Range C:C contains the value that we want to return. And we want to return the value where F2 is found in range A:A.

VLOOKUP

Another alternative is the VLOOKUP function. VLOOKUP is avoided by many spreadsheet users because if you are not the formula might break if you start moving columns.

It still works perfectly fine as a lookup function if you are diligent about keeping your columns in their original place.

Here is the syntax for using VLOOKUP:

=VLOOKUP(lookup_value, range, index, 0)

• lookup_value – the value we are searching for
• range – the table or range that we are searching in
• index – the number corresponding to the column that you want to return data from (1 for A, 2, for B, 3 for C. etc.)

The formula used in this example is: =VLOOKUP(F2,$A$1:$D$5,3,0)

This formula is searching for the value in F2, in cell range $A$1:$D$5, and returning the matching value from the third column (column C).

Closing Thoughts

There isn’t a perfect XLOOKUP equivalent in Google Sheets, but there are many useful alternatives depending on what you are trying to get out of the function.

My personal favorite is FILTER because this is the more powerful of the ones that I have covered, but sometimes the best one is the one you are most comfortable with. 

Give them all a shot and see which one you end up preferring. 

More Google Sheets Tutorials:
How to Use Array Formula with VLOOKUP
How to Use the SWITCH Function
How to Do a Reverse VLOOKUP