Slicing Data with XLOOKUP in Google Sheets

You can dynamically isolate specific subsets of data from a larger dataset, a process known as slicing data, using the XLOOKUP function in Google Sheets.

Typically, we use functions such as FILTER and QUERY for slicing data based on multiple criteria, such as date range, product category, price range, or customer location.

Here is a scenario where you can’t use these functions to filter the data without the support of additional functions.

CategoryItemsPriceIn StockMinimum StockMaximum Stock
ToolsWrench1518830
Pliers2012622
Painting SuppliesPaint Brush2251050
Roller7151025
Office SuppliesPen16030125
Notebook34020100

In this table, how do you filter the items that fall under the “Tools” category?

Two items fall under the said category: “Wrench” and “Pliers”. To improve readability, the category name is not repeated in the first column, making criteria-based filtering difficult.

This highlights the importance of data slicing using XLOOKUP.

Here is an XLOOKUP hack where we will extract specific data ranges by lookup. This method uses two XLOOKUP functions with the colon (:) range operator in between.

Vertical Data Slicing Using XLOOKUP in Google Sheets

Our sample data is in A1:F, and A1:F1 contains the following field labels:

Category | Items | Price | In Stock | Minimum Stock | Maximum Stock

Assume I want to look up the category column and slice the data from “Items” to “Maximum Stock.” The objective is to extract data related to the Tools category.

XLOOKUP for Dynamic Data Extraction in Google Sheets

Below you will find the step-by-step instructions to extract data dynamically using XLOOKUP in Google Sheets. But first, here is the syntax of the function for your quick reference:

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Step-by-Step Explanation

1. Starting Point of the Range:

XLOOKUP("Tools", A2:A, B2:B)

This formula searches for “Tools” in A2:A and returns the corresponding value from B2:B. This will return the starting point of the range to slice.

2. End Point of the Range:

XLOOKUP("Painting Supplies", A2:A, F2:F)

This formula searches for “Painting Supplies” in A2:A and returns the corresponding value from F2:F. This will return the end point of the range to slice.

Key Points:

  • The lookup_range in both formulas can be any column.
  • The result_range controls the slicing start and end points.

Dynamic Range Formula:

To convert this to a dynamic range, place the colon operator in between the two XLOOKUP functions:

=XLOOKUP("Tools", A2:A, B2:B):XLOOKUP("Painting Supplies", A2:A, F2:F)

This will slice the data from the first lookup value to the last lookup value.

Adjusting the End Point:

If you don’t want the last row, use OFFSET with the end point (second XLOOKUP) to offset 1 row backward:

Vertical Data Slicing Using XLOOKUP in Google Sheets
=XLOOKUP("Tools", A2:A, B2:B):OFFSET(XLOOKUP("Painting Supplies", A2:A, F2:F), -1, 0)

In this formula, the OFFSET function follows the syntax OFFSET(cell_reference, offset_rows, offset_columns, [height], [width]), where offset_rows is -1 and offset_columns is 0.

This way, we can dynamically slice data using XLOOKUP in Google Sheets.

Horizontal Data Slicing Using XLOOKUP in Google Sheets

You can apply this XLOOKUP hack in a horizontal lookup as well.

For example purposes, I’ve transposed the above sample data, and the range is 1:6.

Here is the formula that slices the range as earlier:

=XLOOKUP("Tools", 1:1, 2:2):OFFSET(XLOOKUP("Painting Supplies", 1:1, 6:6), 0, -1)
Horizontal Data Slicing Using XLOOKUP in Google Sheets

Explanation

1. Starting Point of the Range:

XLOOKUP("Tools", 1:1, 2:2)

The first XLOOKUP searches for the key “Tools” in the first row and returns the corresponding value from the second row, which is the starting point of the slicing range.

2. End Point of the Range:

XLOOKUP("Painting Supplies", 1:1, 6:6)

In the second XLOOKUP, the search_key is “Painting Supplies.” It searches in the first row and returns the value from the sixth row, which is the end point of the slicing range.

3. Adjusting the End Point:

OFFSET(XLOOKUP("Painting Supplies", 1:1, 6:6), 0, -1)

This time the OFFSET function offsets 0 rows and 1 column backward.

This technique provides a powerful way to dynamically slice and extract specific data ranges in Google Sheets. This data-slicing method can leverage advanced lookup capabilities to slice data accordingly.

Resources

Here are some other XLOOKUP resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.