How to get Match to return values that are visible only

[nawdrizzy]

Hello,

I am trying to Match to different worksheet together and one of the worksheet is filtered. My problem comes when I used the match formula it also reference the filtered cells. Is there a way that I can get it to reference the visible cells only?

 

[Eric W]

Welcome to the forum.

About the only function that is aware of hidden rows is SUBTOTAL. Assuming your current formula is something like E2, you can use the formula in F2 to do the same thing but ignore filtered rows (row 3 in the sample below is hidden, but has "a 2" in it):

	A	B	C	D	E	F
1	Name	Value		Name
2	b	1		a	2	4
4	b	3
5	a	4

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E2 =INDEX(B2:B10,MATCH(D2,A2:A10,0)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=INDEX(B2:B10,SMALL(IF(A2:A10=D2,IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A10)-ROW(A2),0))>0,ROW(A2:A10)-ROW(A2)+1)),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[nawdrizzy]

Hello Eric, Thanks for the response. This did not really help me resolve the issue. Let me try to provide further clarification.

A B A B =math(A1,A1:A6,0)
Invoice# Invoice Amo invoice# Invoice Amo
1. 962535 203.00 962343 302.00 3
(2.965845 102.50)-Filtered 964125 639.94 6
3 962343 302.00 215461 402.00 4
4 215461 402.00 945823 501.40 5
5 945823 501.40 962535 203.00 1
6 964125 639.94 965845 102.50 2

The issue I am having is that row 2 is suppose to be filtered and hidden so for the formula sheet I should see "N/A" because it's not visible on the first sheet. On the formula sheet i see the value which was suppose to be hidden. is there a way that Match can only evaluate cells that are visible only?

 

[Eric W]

MATCH can't tell if a cell is visible or not. You have to use a different set of functions. The formula I showed before should work for you. I've modified it so that the references on a different sheet include the sheet name, and I included IFERROR to show when there's no match:

=IFERROR(INDEX('Data Sheet'!B2:B10,SMALL(IF('Data Sheet'!A2:A10=A1,IF(SUBTOTAL(103,OFFSET('Data Sheet'!A2,ROW('Data Sheet'!A2:A10)-ROW('Data Sheet'!A2),0))>0,ROW('Data Sheet'!A2:A10)-ROW('Data Sheet'!A2)+1)),1)),"No match")

Change the references to match your sheets, and confirm with Contol+Shift+Enter.

 

[nawdrizzy]

Thank you for the help. The formula you provide is a little bit over my head. I do appreciate you taking the time to respond.

 

[dinkink]

Hello,

I am trying to Match to different worksheet together and one of the worksheet is filtered. My problem comes when I used the match formula it also reference the filtered cells. Is there a way that I can get it to reference the visible cells only?

It may not be the most elegant solution, but in my case my main purpose for using Match was to get alternating odd/even numbers so I could use conditional formatting across changed values. To do this I created a column with numbers, then a new column that used subtotal(109,[array]). There's a whole bunch of hidden items in this table.

I'm sure I could make this more elegant, but good enough is good enough for my scenario. And hopefully is easy to follow.

PrimaryIndex - This is getting the position in the list of Unique items in the Primary column (including hidden items).
sbtl - function number 109 ignores hidden items. So for all rows that are hidden, this will return 0. Ultimately we can't see this, because the row is hidden SUBTOTAL function - Microsoft Support
Alternating - gets the position number of unique items in subtotal column. Any items that are hidden have 0 for their subtotal, so visible items start at position 2).