How To Xlookup In Google Sheets

by

In this tutorial, we will delve into the powerful feature of Google Sheets known as XLOOKUP. XLOOKUP is an incredibly versatile function that allows you to lookup and retrieve data from a table or range based on a specific criterion.

XLOOKUP is not native to Google Sheets like it is in Excel, however, with a combination of functions, we can replicate the same functionality.

Getting Started with XLOOKUP Function

Let’s consider the following data set, where column A contains the Product ID, column B contains the Product Name, and column C contains the Price:

A  B              C
1  Product ID    Product Name    Price
2  PRD001        Apple           $1
3  PRD002        Banana          $0.5
4  PRD003        Cherry          $2
5  PRD004        Date            $3



Suppose we want to find out the price of a product given its ID. Here’s how we can use XLOOKUP in Google Sheets:


Step 1: Define the Lookup Value


The lookup value is the specific value that you want to find in your data range. In our case, let’s say we are looking for the price of the product with the ID ‘PRD002’. Our lookup value will be ‘PRD002’.


Step 2: Define the Lookup Array and Return Array


The Lookup Array is the range of cells where the Lookup Value is supposed to be found. In our example, since we’re looking for the Product ID, the Lookup Array will be column A.


The Return Array is the range of cells from where the data will be fetched once the Lookup Value is found. We want to fetch the price of the product, so column C is our Return Array.


Step 3: Use INDEX and MATCH Functions


Since Google Sheets does not have an inbuilt XLOOKUP function, we will use a combination of INDEX and MATCH functions to achieve the same result. The syntax will look like this:


=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))



In our example, the formula will look like this:


=INDEX(C2:C5, MATCH("PRD002", A2:A5, 0))



When you hit enter, it will return the price of the product with ID ‘PRD002’, which is $0.5.


Conclusion


And that’s how you can perform an XLOOKUP in Google Sheets using INDEX and MATCH functions! Although XLOOKUP is not directly available in Google Sheets, with the right combination of functions, you can replicate the same functionality and power up your data analysis.