How do I apply the MATCH() function only to a filtered range? Or is there another way to do that?

Question

I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:

A (with auto-filter against "2")
0
1
3
4
5

Now I want to find out whether one of the values in another sheet is part of that filtered list. Yet, with MATCH(2;A;0) I still get the output 3 even though this row 3 is not in the filtered range.

The MATCH() function does not know the filter, that is why I get match results that are not in the filtered range.

How do I apply a sort of MATCH() function only to a filtered range? Or is there anything else that leads to the needed output?

As it turns out from the answer, this question is almost the same for Microsoft Excel (with the very small change in newer Excel versions that you may not need the matrix trigger Ctrl+Shift+Enter anymore, but just Enter). That is why this gets an Excel tag as well.

Answer 1

If you are using MS365 then you could make the above formula shorter using LET() and SEQUENCE()

---

For Older Version of Excel -->

• Formula used in cell G5

=MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!$A$2:$A$7,
  ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2),0,1))>0,
  IF(Sheet1!$A$2:$A$7=Sheet1!F5,1)),0)

---

Or, In MS365

• Formula used in cell G6

=LET(α,Sheet1!A2:A7,MATCH(1,IF(SUBTOTAL(3,OFFSET(α,SEQUENCE(ROWS(α))-1,0,1))>0,IF(α=F5,1)),0))

---

• So basically, what I am trying to achieve using the SUBTOTAL() function is to get the list of values which are not hidden or filtered out, in the following function the 3 denotes the COUNTA() while OFFSET() helps in creating a dynamic range. The following screenshot and the formula will give you a clear idea. Also since I am on MS365 hence dont require to hit CTRL+SHIFT+ENTER, but older versions needs to hit.

---

=SUBTOTAL(3,OFFSET(Sheet1!$A$2:$A$7,ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2),0,1))

---

The above is same as

=SUBTOTAL(3,OFFSET(α,SEQUENCE(ROWS(α))-1,0,1))

---

Where α is the range Sheet1!A2:A7

---

• From the above screenshot, its seen that, the one which is filtered out i.e. 2 shows as 0, because those which are found are the 1s, when this part of the formula is evaluated it shows as all FALSE since it cant find the matching value here 2

---

• Hence, when wrapping within MATCH() to get all the TRUE i.e. 1s we are getting #N/A since it cant find it.

---

But if we try to find some other values, it will give us the required output by showing the row position. The logic applied goes same for other versions as well as for LibreOffice as well.

---

Now the check by the questioner for the German Libre Office (works):

• Go to G5.
• Paste VERGLEICH(1;WENN(TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1))>0;WENN($A$2:$A$7=F5;1));0).
• Press Ctrl+Shift+Enter.
• See the matrix ({}) function {=VERGLEICH(1;WENN(TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1))>0;WENN($A$2:$A$7=F5;1));0)}:

And to understand the main trick, here is how the subtotals work in the matrix:

• Mark G7 to G12 (or any other 5 empty cells below each other).
• Press F2 while the 5 cells are marked.
• You will now be in the first cell of the 5 cells.
• Paste TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1)).
• Press Ctrl+Shift+Enter.
• See the matrix of the 5 cells filled with 0 in G9 for the 2: