How to Use XLOOKUP in Google Sheets (With Example)

The XLOOKUP function in Excel can be used to look up a value in a range and return some corresponding value.

For example, we can use the following XLOOKUP function in Excel to look up the value in E2 in the range A2:A11 and return the corresponding value in the range C2:C11:

=XLOOKUP(E2, A2:A11, C2:C11)

Here’s how to use this function in practice:

The XLOOKUP function looks up the value “Rockets” in column A and returns the corresponding value in the “Points” column.

Unfortunately, Google Sheets doesn’t have an XLOOKUP function but you can easily replicate it by using the FILTER function instead.

You can use the following basic syntax to do so:

=FILTER(C2:C11, A2:A11=E2)

The following screenshot shows how to use this function in practice with the same dataset in Google Sheets:

XLOOKUP in Google Sheets

Here’s how the FILTER function works:

  • The function looks at the range C2:C11.
  • The function then returns the value in the range A2:A11 that is equal to the value in E2.

Notice that this function returns the exact same value as the XLOOKUP function in the previous example in Excel.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets: How to Extract Top N Values from Range
Google Sheets: How to Filter for Cells that Contain Text
Google Sheets: How to Extract Numbers from String

Leave a Reply

Your email address will not be published. Required fields are marked *