XLOOKUP Visible (Filtered) Data in Google Sheets

Have you ever found yourself struggling to work with filtered data in Google Sheets? Specifically, have you come across the limitation of the XLOOKUP function not being able to exclude hidden rows? Well, fear not! In this article, we will explore a clever workaround using the SUBTOTAL function to XLOOKUP visible data in Google Sheets.

Introducing XLOOKUP and its Limitations

XLOOKUP is a powerful function in Google Sheets that allows you to search for a key/value in a range and retrieve the corresponding value from another range. However, one thing it doesn’t provide is an option to exclude hidden rows. This becomes a problem when you’re working with filtered data and only want to perform the lookup on the visible rows.

The Solution: XLOOKUP with SUBTOTAL

To overcome this limitation, we can combine the XLOOKUP function with the SUBTOTAL function. SUBTOTAL is capable of identifying hidden rows and can be used to create a formula that only considers the visible rows for the XLOOKUP.

How to XLOOKUP Visible Data in Google Sheets

Let’s dive into a step-by-step guide on how to use XLOOKUP with SUBTOTAL to work with visible data in Google Sheets.

Sample Data (Un-filtered)

Before we get started, let’s take a look at our sample data. We have a list of codes in column A and their corresponding quantities in column B. For the sake of simplicity, we haven’t applied any filters to the data yet.

To get the quantity of the first item ending with “1003”, we can use a wildcard match in the XLOOKUP formula as follows:

=XLOOKUP("*1003",A2:A,B2:B,"Not Available!",2,1)

Here’s a breakdown of the syntax used in the formula:

• search_key: “*1003”
• lookup_range: A2:A
• result_range: B2:B
• missing_value: “Not Available!”
• match_mode: 2 (wildcard match)
• search_mode: 1 (from first value to last value)

Now, let’s see how we can modify this formula to work with filtered or visible data.

Sample Data (Filtered) and XLOOKUP in Visible Data

To filter our data, we can use the “Create a filter” option under the “Data” menu. Once the filter is applied, we can filter column A using a custom formula in the “Filter by condition” section.

=not(regexmatch(A2,"A"))

As you can see, the original XLOOKUP formula in cell D1 doesn’t exclude hidden rows and returns a value from a hidden row. To XLOOKUP visible data, follow these steps:

1. Use the following BYROW formula to identify filtered data:

=BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range)))

2. This formula will return either 1 or 0 in column A, indicating whether each row is visible or hidden.

3. To exclude hidden rows in XLOOKUP, add 1 to the search_key and append the BYROW formula to the lookup_range:

=ArrayFormula(xlookup("*1003^1",A2:A&"^"&BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range))),B2:B,"Not Available",2,1))

And there you have it! With this modified formula, you can XLOOKUP visible (filtered) data in Google Sheets.

Conclusion

Working with filtered data in Google Sheets can sometimes be challenging, especially when using functions like XLOOKUP. However, by combining XLOOKUP with the SUBTOTAL function and a clever formula, you can easily overcome this limitation.

So the next time you find yourself in a situation where you need to XLOOKUP visible data, remember this handy trick. For more tips and tricks on Google Sheets, be sure to visit Crawlan.com.