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:
The following screenshot shows how to use this function in practice with the same dataset 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.
The following tutorials explain how to perform other common operations in Google Sheets: