We can XMATCH in visible rows (filtered data) without using a helper range in Google Sheets. The formula will exclude hidden rows.

This method only applies to match search keys in a column and return their relative positions if found or the #N/A error values.

The relative position may not be vital in most cases. We usually use the output to know whether a value is present in a list in a column.

But, if you are particular about the relative position, you must know what happens to it when you hide rows.

I will explain this first, and then we can go to how to XMATCH visible rows in Google Sheets.

Hidden Rows and Relative Position

Below you can find a few dates in cell range B2:B11 and their relative positions entered in A2:A11 for your reference.

The relative position of the date 24/12/2022 in B2:B11 (lookup_range) is 6 if we use the formula to search it from the first entry to the last one.

Formula # 1 (E4):

=xmatch(E3,B2:B11,0,1)

If we hide row # 7, the XMATCH output will be the same as it doesn’t have the option to omit hidden rows.

How to get the relative position of the search key in the next visible row?

That is what we meant by XMATCH visible rows in Google Sheets.

Let’s hide row # 7.

The formula should match the search key in visible row # 9 and return its relative position, which is 8 if you count all the rows and 7 if you count only the visible rows.

We have solutions to both.

1. XMATCH Visible Rows and Count All Rows

Similar to XLOOKUP Visible (Filtered) Data, we require to use BYROW, one of the LAMBDA Helper Functions, and SUBTOTAL to add the said functionality to XMATCH.

We will use the following helper formula result to create a virtual lookup_range within the XMATCH to return the relative position of the visible rows.

Helper Formula:

=BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))

It would return 1 in visible cells and 0 in hidden cells.

Note:- If you want to learn this formula, check the XLOOKUP tutorial mentioned above.

How do we use this helper formula in combination with XMATCH then?

We will virtually replace the dates in B2:B11 with blank if the helper formula returns 0, else B2:B11, and use that as the lookup_range within the XMATCH.

Generic Formula (Virtual Lookup_Range): if(helper_formula_result=0,"",B2:B11)

Formula # 2 (E5):

=ArrayFormula(xmatch(E3,if(BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))=0,"",B2:B11),0,1))

We must use the ARRAYFORMULA function because of the non-array IF logical test in an array.

Syntax:

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

search_key: E3

lookup_range: if(BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))=0,"",B2:B11)

match_mode: 0

search_mode: 1

2. XMATCH Visible Rows and Count Visible Rows

Here also, we must modify the lookup_range using the BYROW helper formula.

Instead of the IF logical test, here, we will use the FILTER function.

We will filter visible rows (helper formula output >0) and use that as the lookup_range.

That will help the XMATCH to match the search key only in visible rows and return the relative position accordingly.

Here the ARRAYFORMULA function is not required, as FILTER can handle arrays.

Formula # 3 (E6):

=xmatch(E3,filter(B2:B11,BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))>0),0,1)

Relative Position When Searching From the Last Entry to the First

When we have the latest records at the bottom of the table, in some cases, we may require to search from the last entry to the first entry.

What changes should we make to the formulas above?

The above formulas have no problem handling that.

Replace the search_mode, the last argument in the function, 1 with -1.

Keep row # 7 hidden and modify formulas 1 (E4), 2 (E5), and 3 (E6) as mentioned above.

The first one will return 8, and the other two XMATCH visible rows formulas will return 7 and 8, respectively.

Test it in your Sheet to understand it even better.

That’s all. Thanks for the stay. Enjoy!